Video: Understanding Security and Network Connectivity

Presenter:Bob Tabor (from LearnVisualStudio.NET)

SQL Server beginners will learn to differentiate between SQL Server database files and SQL Server processes. Explore scenarios for connecting to SQL Server Express databases, enabling and disabling user instances, login permissions, and other security precautions.


Click here to go to original video page

The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0
When you create a database and then create tables and work with the data itself in that database all the information is stored within a single MDF file that resides on your computer s hard drive however there s a layer of software or a Windows service that manages your interaction with the databases on your computer and make sure that your data is available data security and it provides network connectivity from other computers if you desire that this process runs in the background and it s always available waiting for your commands I want to start here because of until now we willing to talk about the structure of the database and the data itself which as is looked at is just one aspect of SQL Server 2005 express edition and SQL Server service or process is extremely important because as the gatekeeper for your databases when you start consider how it is that your database will be
Minute 1
Is within your application and how users will connect to it this distinction becomes very important so what are your options when you start consider how your application will communicate with the database or what if you need each user to have their own copy of the data so that there s no sharing between the users or one of the neat users on different machines to all share the same data through the network to their two basic connectivity scenarios that the express edition of SQL Server 2005 supports like all other versions of SQL Server 2005 you can allow other computers all local or global network to access the data in the database objects contained within the MBF files on your computer in this scenario all the users can work simultaneously with the same copy of the database they re sharing the same data so if one user makes a change to a given database record all the other users will be able to see that change as well known order to put
Minute 2
At the integrity and confidentiality of data as well as the databases objects a measure of security is employed by SQL Server to authenticate each user once each user is authenticated meaning that they can prove their identity through the use of maybe username and password for sample of the group permissions are granted to the user authorizing user perform certain actions while interacting with the database where sophomore bow concurrent multiuser access as wolves multiuser security in just a moment the second scenario is you need to the express edition of SQL Server 2005 it allows multiple users on the same machine to each work with their own private version of the database their own copy these are called user instances the benefit of user instances that it s very easy to enable because you have the same authentication and authorization concerns so I want to stress that this scenario
Minute 3
Eyes when you want to allow one or more Windows user accounts of a single machine to access the database and the users will not need to share the same data so if one user makes a change to give a database record none of the other users will be able to see that change because they have their own private personal copy of that database with user instances securing the database becomes much more straightforward if your user on the local computer you automatically have all the rights you need to do every want to do with the database since each user of the computer gets his or her own private copy the database they can do anything they want to delete things and change things after all they try to something militias they can really only hurt themselves so user instances rely on the Windows operating system s built in authentication and authorization mechanisms so how do you tell SQL Server that you want to allow concurrent
Minute 4
Accuser axis versus user instances was actually really simple you don t have to change anything about SQL Server per se as imports of both of these scenarios all you really have to do is to change the application s connection string in the connection string is a series of name value settings that a new net uses to try locate and handshake with the database among the various settings you can configure the user instance setting determines which of the two scenarios you want when able if you set the sequel to true then each user or will get their own private copy of the database setting it to false in all users who point to the database will be sharing the same instance of the database behind the scenes each time SQL server receives a request to enable user instances via the connection string SQL Server checks to see if values are already has a private copy of the database in
Minute 5
Don t you create a private copy the database as well as all the other tables and settings that SQL Server uses internally but primarily for our purposes of decrease a private copy of the database and use the original copy the database that was installed on the computer again this happens behind the scenes and user my spirits a short delay the first time they run the application but otherwise it s pretty transparent and one less common about user interfaces before we demonstrate how to work with great applications using a other express edition tools like Visual Basic 2005 express edition or visual Web developer 2005 express edition integrated data source object which then creates a connection string in either the app config file or the web config file by default the connection string to create a naval user instances was a sequel to true in the
Minute 6
Isn t it set up this way is to enable the most secure scenario by default that allow you to open a concurrent multiuser access only if you really need it to demonstrate how user instances were created a small example called SQL user rinses is VB even though this is pretty with the Visual Basic 2005 express edition I wrote a fully no code so you can do the exact same thing to see sharp version if you are so inclined essentially what they did was create a database of which I called Saddam a user instance is VB MDF and has one table called sample table and has three fields simple IDE witches and datatype gets a sample data which is a bar chart 50 and more sample data which is another Berkshire 50 the next thing I did was I went to add a new data source which pointed out my new data
Minute 7
File which open up the data sources window I simply went to the sample table I selected data grid view and I dragged and dropped it on my design surface which automatically created not only the data grid view itself but also of some of these other objects you re my form like the sample table binding navigator which allows me to navigate through the records at new records delete records save the changes are made so on as well as a table adapter object in a binding source object the most important aspects for our purposes is the config file whenever we automatically or whatever he created the data source object through that the dragging and dropping technique that I described it automatically created a connection string which is modifiable hearer in my app config file you can see that it
Minute 8
Disses BDE on a connection string and you can see at the end of the connection string we have our user instance studying said by default equal true today so we do changes as we wanted to and enable multiuser access from the exact same copy of the database but we want to show how user database is work so relieved the sequel to true after I got the application working a made sure that it works I went to look a bit old and selected publish SQL user database as his VP and put it into a deploy folder on my seat dries to concede that we have the SQL user database is VB application s go ahead in run the setup while they created formula automatically and now that I ve
Minute 9
A successful install the application will try to run it there s actually no data in here will shut this down for now it do what I want to do next is to go to my computer and right click and select manage or I can go to Don looks to the control panel is low mortgage are friendly to go to user accounts either way I can create a new user account is also learn trying to do here and I want crating accounts were to call this sample user hands of four simplicity sables make this an administrator arguing we make this limited but I want to do some most scrolling through the filesystem so were to make an administrator and it s a great account is he now we have our sample user at which all the stuff down
Minute 10
And diminished down this folder or in this as well and let s do this riddle log off actually going to instead of just doing that will go head to restart computers and after we ve rebooted you can see we knew that we have our new user of the sample user school and login as him and is less try to installer application for this user as well go to Windows Explorer computer to deploy folder for remedy ticket tour and will go to set up
Minute 11
Click install install the application force will try to launch it in the first of his launch its creating on our own private copy of the database and so just to make sure that this works a student sample data will type in to load from sample of user and the will to save button and will close down the occupation now listed as solicitor look at the documents and settings and find those database files so we can see where it actually copies this particular user s version of the databases to do what we needed to access for not seeing all files are there actually available for the tools folder options to view and its we will show
Minute 12
Hidden files and folders will see through this hide extensions setting out click apply and excellent if it applied all folders include guests so now we re going to see all the folders that were previously hidden to the local settings folder and with the apps folder list to drilling down will choose data and justice can be some really weird looking the folder names will just keep going through on finally come to the data folder and now notice that we have a user database is his VB MDF and a log file as well for our database else to Foreign Ministry around the script on this particular user I can go to all folder which is the other log in ideally if you are of enabling least privileged access and not every user is administrative machine but they only have local as access to just their own files
Minute 13
Then this mix of the scenario makes a lot more sense or reduce any way will go to a little settings again to be apps to owe a data is controlled through and we find another copy of user database is his VB India so to prove that were working with two different instances of the same application let s do this this time he will it loss off such user were going to select column Bob is the user this time a real water application and see if we saw the same a record so let s go to our learn Visual Studio net SQL user database is VB application and see we see the record that we have put from the previous user
Minute 14
Edition comes a there s no data so the record that we created whenever we were logged in as the sample user didn t propagate this database very good reason why because it s a whole different instance of the data does hold of a copy as we witness as we went through the Windows file system saw were actually stored those copies at this of that war for user database is his world what he wanted in this stable user database is is how to go about doing that sort of the words you want to allow multiple users on the same computer to be able to access the same instance of the database well as you see here I have a new project called SQL multi users VB again is exactly like the priest example in every way the only change that I made was in the app config file I want to the connection string setting to the very end and set the user database property equal to false in this case the
Minute 15
I built the application and language are deployed and will do differently admittedly ongoing close this down and showed what it did so for Windows Explorer assembly went to my projects will see users and went to the vision release directory and copy all the files out into a directory on my seat drive called SQL multi users VB and then simply ran the application agency aborted on this one so I logged in as Bob ran the application wrote a record logout logged in as the sample user selects which the user to sample user and then ran the application back ago and wrote another record to conceive or sharing the same instance additionally whenever I go down to the SQL server manager express studio
Minute 16
Oki that we automatically get the database attached to our two are of instance of SQL Server express so we can often open to worked with it and so on but the key here is that both users are using the same instance of the database as opposed to their own private instances of the database and we enabled that simply by changing the connection string property so now let s take a look at the first scenario we described the beginning of this video concurrent multiuser access to enable your applications to utilize this you have to do two things to the computer that will host the database first in
Minute 17
After Korea logins and then assign roles for the database using a SQL Server management studio express second if the configure network conductivity which by default is turned off for security purposes also if your computer has a firewall you to create an exception for SQL Server so applications can make requests on certain ports and not have them rejected automatically so let s start with login and roles each login identifies a single user ideally not seen instances where multiple users or applications use the same login not an ideal situation usually wants the granularity said overtime can change the permissions for individual applications or users of me and not at all the other users but back to the point each login then has one or more roles associated with each goal grant permissions to do something on the database will
Minute 18
Just a moment but what is logged when really it s just the way of authenticated and authenticating someone or something it proves that they are who they say they are in SQL Server 2005 there to race a dead we can determine this if your application is running within a private Windows network luck at a company in SQL Server 2005 can use each user s Windows login account to determine who they are this is the preferred way to configure SQL Server for use because it lies on Windows existing authentication mechanism however this is an application that resides on a more public network like the Internet then SQL Server must manage its own authentication yet to create usernames and passwords for chooser and this is called mixed mode regardless of the authentication mechanism whether you re using Windows mode or mixed SQL Server mode you must next decide what permissions are available to each log
Minute 19
That you go user by user and assign permission by permission but that would take a long time if you have a lot of users and permissions to configure instead he can use roles each role has a set of permissions already defined and you simply assign roles to each login which makes management a lot easier of SQL Server comes already with some built in roles like SysAdmin or rather system administrator and that will allow you to do anything across of the entire cluster of databases that are hosted on that particular server in great new databases you can crate logins and assign roles you have essentially the right to do anything across any database another role would be DD owners somebody who is responsible for a single database on a given server
Minute 20
Database objects and interact with the data itself in any way but then again limited to a single database to further restrict permissions you might have a DB data writer who can both read and write to the databases data but not be able to change any of the deadly subjects of tables for seizures and then probably one of the most restrictive of the built in roles is the DB data reader who can essentially just look at the data is authorized to look at the data a but can make any changes to it that you can decide which of these to use within your applications as you start assigning roles to various permission thoughts is very as logins of people who were eager going to let and allow use the application
Minute 21
Old so you have that option so to demonstrate how to create roles in logins within the SQL Server management studio express eyes popped open and what I would do is cradle login and then assign that login DTD owner permissions on the my company database so let s start by going to the server and clicking the security folder in a city where 40 gods in several logins most of these are built in logins and you can see that there are either used by SQL Server internally or in the case of Superman role is how we login using the local Windows account only want to create a account so logins so that somebody can login to my database server over the Internet so we re going to create a mixed SQL Server manage login and to do them right
Minute 22
Click and select new login items going to create a login name else have to call it Bob and weirdo switch from Windows authentication which I would use again if our only going to access this over a private network where I have a domain controller I would as to make controller log and all those things were going less SQL Server manage this is we are all publishing this out to the Internet won t be able to access it remotely and we will create a password so the type of the heroically enforced as repulsive as good enforced password expiration was good enough to require that the user changes the next login when you set the default database with these were all be logging into automatically reset that to my company and that s about the only options will be the change we can if that we want to for sample make this person or his login a
Minute 23
Is an administrator we might go to the server roles tab or page and then select no a more server scoped roles for this particular login or not I give this particular login any of these system level options instead were going to sign this login the role that will allow it to be the DB owner for my database now to go to the databases find to my company database and scroll down until we find the security folder for my company database and open up the users folder and right click on users and select news user and here we re going to tie a login that we created with a role or one or more roles were going to give
Minute 24
This collection of a login name and the roles of its assigned a name as well in this case and recall the same things are called off and down a cliff under login name and choose the browse button and conceit office when the list of logins that we could do to Simona Godel and click okay and okay and then what I need to do is find the role membership for the role that we want this user to be assigned to you and we can assign him a given multiple roles the worst stomach of the TB owner and then click okay so to really test of this work for needed to access this computer from a different computer use the login credential information that we created at the server level and to make sure that that will login
Minute 25
Has the DD owner role so they can make changes to my company database so that s a great segue into our next area discussion now we want to look at and demonstrate how to configure network access so that users on other machines can access SQL Server on this machine and access the resource databases that are managed by this instance of SQL Server to you install SQL Server 2005 express edition by default it s not configured to be accessed over a network connection the reason why set up this way is to enable the most secure scenario by default which is only allow all users on the same computer as the it s a SQL Server to access the data that allow you to open up network connectivity if you really needed and the way that you compost that is through the use of SQL Server configuration manager said we ll do that right now
Minute 26
Open up the configuration manager will go to starts all programs find Microsoft SQL Server 2005 full service configuration tools folder and then SQL Server configuration manager the first and that we need to do whenever we look at the configuration managers decide whether number to use the SQL browser service the benefit of having it turn Don is that users that are connecting remotely don t have to specify the port in their connection string but it is a security best practice to not run the SQL browser service if you turn it off and it eliminates the need to listen on a DVD UDP port of reducing the attack surface of your SQL Server instance so let s go ahead and select it and click the stuff service button and XML want to do is configure which recalls one would enable
Minute 27
Of analog connection on so we don t allow a connection on TCP IP as you can see by default it s disabled the only thing that is enabled a shared memory which makes sense because by default SQL Server only allows local connections and we have to configure it to open it up to network connections so let s go ahead and enable TCP IP by right clicking on it and selecting in April and is really as easy as that will have to restart the service will do that just a moment but what will probably want to do is also take a look in the properties specifically the IP addresses tab and make sure that we ve got our IP address for our computer are specified there ideally we d have a static network IP address for SQL Server so that there is some consistency whenever our users try to connect in a change connection string every time so just wanting keep them on their hadn t click the OK button and
Minute 28
Auel wanted to school or services selection left hand side and restart our SQL Server express process and this is it a few moments we encountered problems while stopping restarting the service you can always reboot the computer so you get to the server configuration manager would start all programs the SQL Server Microsoft SQL Server 2005 older configuration tools folder and funny icon for SQL Server configuration manager and so on the first zoning to do is determine whether or not we need the SQL browser service to be running or not solicit a look on the left hand side at these services are currently running a sea that I have the SQL Server browser running right now for the benefit of having the service runs that the users connecting remotely won t have to specify the port in the connection string also it allows a
Minute 29
Of applications like SQL management studio express able to access your database because it uses UDP in the background but it s a security best practice to not run the SQL browser it reduces the attack surface area by limiting the need to listen on the UDP port so that s really up to you and what your intent is proper versus real leave it running butts if you can get by without it you should turn it off if you intend to use management studio express remotely from a different computer you ll have to leave it on this and will do is go to the configuration protocols for a sequel is for us you ll note that by default we have shared memory enables the rest of the protocols are disabled but we want to enable TCP IP or right click and select the enable it says that the change won t be made until the restart our SQL Server service
Minute 30
Is great before I do that though I want to take a look at the properties and the only thing that s really important properties tabs is making sure that your IP address is set correctly ideally this computer has a static IP address and so you would put the IP address here and make sure that shows up correctly scored then click OK and move on to restarting our services to Dems to right click and select restart it also just restart the computer and so if you run any problems the service won t restart for a reason just go head and reboot the computer and all work is fun if you run into any problems with conductivity may want to look here make sure that of the services are in fact running and if they re not in star mode automatic you may run into some problems there if you need work through some of those issues are encouraged to take a look at the hell
Minute 31
Or a take a look at the MSDN that s available from the Microsoft com website so just that since we configure the network access within SQL Server doesn t necessarily mean they were all ready to go if you re using firewall software don t have to configure it to allow requests of in the case of the SQL Server browser recorder have to enable certain ports to be used as well so to do that will have to configure some exceptions if you re using Windows far wall and Windows XP you ll need to create an exception and we ll show you how to write now to start control panel and security Center and this is one means of getting to the Windows far wall of dialogue boxers coupled with wishing to it will go to the exceptions tab gambling wanted to first ball is at an exception for the SQL Server service itself so as to hint at
Minute 32
Program and click browse I had already browse to this directory I have been no doubts the SQL Server service on my to do a computer is on the sea drive under program files Microsoft SQL Server and the SQL 1 and a sequel in the directory saw select SQL Server are exe and click open and click okay now from going to enable the browser service all have to add exception for that as well so let s go ahead and click the add program and browse this time her into the back little bit code to 90 folder shared and SQL browser exe so just finished a progression there on my particular computer its SQL program files Microsoft SQL Server 90 erred in that
Minute 33
Trim looking for the SQL browser exe and I m also the need after UDP ports so we ll go and click add ports and we called us what we want and the poor we need is 1434 so let s put out 1434 in the Port number text box and select UDP and click okay and click okay and then close down our security Center and a control panel and now we want to do is go to a second computer and try to access this instance of SQL Server using the management studio express on that other computers let s do that right now so not looking at another computer on the network school had an open up the mess the studio express
Minute 34
And when a change from this default setting which is of the local computer and will change that to the IP address 192 to 168 1 15 on my particular network SQL express will change from Windows authentication to SQL Server authentication recall that we created a login name law and type in the password now mature type it correctly and then we ll click connects and with any luck will connect over the network to our for my company database where I am the given the role the DB owner for this particular database I m not a DB owner for the other databases on this network and you can see as a threat access fact that I get it s not accessible because I don t have the proper permissions the proper role to act
Minute 35
That database the last thing I want to try to do is use the SQL multi users VB application that we use a few moments ago and modify the connection string so that it points to the database on the other computer the original computer that were working with slow switch back to that computer right now and this will do is you need to change for this particular database were going to add the user Bob make him a DB on this database as well so find here give him until he can keep younger I d also just give him one of the lesser responsibilities I get a writer if I want to
Minute 36
Punt and now that I ve made the login bought a user on my multi SQL multiuser VB MDF file on Google back to the other computer knowledge changed the connection string to point to this database and log in as this user saw back on the remote computer right now and is open up the config file as a matter of fact or he got it open and I change a connection string a change couple things about her first loss of the data source equal to the IP address SQL express our instance of SQL running on a computer I got rid of the DB attached name and instead use initial catalog equal in the name of the database down this particular case the name of the database happens to be the path as well we may need to go back and change that is usually at practice to have that all laying out the Urso with
Minute 37
Echo back to the other computer change name of the database of whom are currently and less insecure by telling the exact path and file and so forth and then I added in userid UID equal Bob and the password DWG equals the password not encrypted this whole connection string as well so as to of make this non human readable and therefore habitable and they want to do that in the future but for right now is a test to see if this works so I copied the SQL multiuser application decided in the previous example few moments ago toward this up computer and you just saw how I change the configuration file now is simply just run our SQL multiuser VB exe and we do we get the data that you recall we saw all a few months ago whenever we ran this on the other computer so as to
Minute 38
Mold of effort but we were able to successfully enable concurrent multiuser access from different computers by simply making a few settings and within the configuration tools of enabling communication through the firewall and a making of changer to to our connection string within the application on a remote computer before the close was consider an ASP net application with the scenarios is true for web application is a treelike user instances or concurrent multiuser access at Raleigh potentially a thousand different users to have to create how to SQL Server for each one will not exactly while you may have a number of different people connected your web application of Web server software Internet information services uses a single Windows log and represent all those users so they have developed a web application using visual Web developer to the
Minute 39
About express edition utilizes a SQL Server 2005 express database when it creates a connection string for you it will automatically set user rinses equal to true in other words are nothing special you have to do it in SQL Server or on your Web server it just a just one more thing to note if you re just getting started it can be welcomed using to differentiate your databases permissions like we been discussing for the last half hour or so and your applications permissions you may well limit who can access certain webpages on your website and use early can store those logins in the table in your database the way you made John if he views the ASP net Web configuration utility and he saw created the ASP net DB MDF file in the project about the way your application can check the table in your database to see those logins and permissions is by using a single Windows logon account so
Minute 40
Just know that limiting access your database and your application are two totally different things you can store your applications log information in the database but only one Windows user can access the database and as we close I just want to reiterate that in most cases especially to getting started or to be building small web applications that reside on a single server you won t need be concerned about some of his more dance functionality that we talked about today like multiple users on a single machine or multiple users on multiple machines but I want you to be aware of how this works and demonstrate how SQL Server 2005 press fishing can handle these different scenarios okay so were getting close to the end just one more video lasts in this series in the next few real for all of this knowledge that would gain over the previous seven lessons together for them to build an entire application from scratch don t miss that one