Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
One of the best practice for defining your principals is to make sure that they already exist as users on your domain or on your local machine and that way we're using Windows authentication as that first barrier to entry, they have to have logged on to your machine or to your domain to get there, it's not the only choice that we have. When I'm creating a new login one of the options I'll have here is to switch from Windows Authentication to SQL Server Authentication. This is not a minor difference. This is a big deal.
With Windows Authentication selected the login name that I type has to exist outside. It must exist on this machine or on that domain. When I select SQL Server Authentication it really means define a new user, give them a name, give them a password. They didn't exist before. I'm just writing them and they may now exist in SQL Server. A very common reason to do this is that you have, for example, a web service application, something that needs to connect to this machine, but because of the way your network is setup it can not actually authenticate as legitimate Windows user.
They might be running on a Linux box, they might be running as a Java application. They're outside your firewall and they can't logon as a Windows user. They need to connect with a username and a password. So we'll set this up. We'll give them the name WebServiceApplication and I'm going to type in a password here. I'm going to uncheck the user must change password at next login. In case this was an application was connecting, we don't want to throw that problem in their way, and it's up to you whether you want to enforce password expiration and so on.
All the other choices that we have are the same. Do I want to give this new user any server role? And no I don't and in fact the only thing I'm going to give them is the ability to read from AdventureWorksLT. So I'll switch to my User Mapping, I'll select the box beside AdventureWorksLT, and I'll give this user the db_datareader role and click OK. If I expand Logins I'll see that user listed there. Again this user has not been created on this machine, he is not a Windows user, not a domain account.
He only exists inside SQL Server and the question is well, prove it. What can that user actually do? Well we can actually emulate that and try and log on to this database as that user. I'm going to click this button up in my Object Explorer that says Connect Object Explorer and it throws up the usual dialog box we've seen. What I'm going to say is instead of connecting as me I want to connect to the same machine using SQL Server authentication, and I had an old account there, but I'm going to type WebServiceApplication and type in the password that I had and click Connect.
We're going to have a problem. Now it just says login failed for user WebServiceApplication and I perhaps could think maybe I type my password wrongly, maybe I type the name wrongly, does it need some prefix, and unfortunately the problem is a bit bigger than that. Here's the issue. If I go back and I'm still connected as myself right now, so I do have admin privileges. I'm going to right-click the Instance and click Properties. I am looking at the properties of SQL Server instance on this machine. Now if I come down to Security I'll see that my server authentication is a Windows Authentication mode. That means in order to log on to this machine I have to be a Windows account.
Now you might think, well why did it let me create a SQL Server account then? Well, it could be useful. You might want to set up several SQL Server accounts but you're not actually going to switch your authentication mode until you're ready to go live. So it's perfectly acceptable. But I need to switch it so I can check it. You can do this after the fact, but it's a fairly significant switch. You wouldn't want to do it in the middle of a busy day because as soon as I select this and click OK, it's going to tell me that my configuration change will not take effect until SQL Server is restarted.
Well it's just me so I can restart it. I'm going to drill down into my SQL Server 2008 R2 programs, find my Configuration Tools, and open up SQL Server Configuration Manager. The only thing I need to restart is the SQL Server Engine itself which shows up as this SQL Server address and I have the choice of hitting the Stop button and then waiting and then clicking Play, but we do have a little Restart Service buttons as well. Right-click would work too. This will take just a moment because it's a small server, and when it's done we can close Configuration Manager.
That's the only thing we needed it for and I'm going to try to connect again. I'll click this button. I'll say that I want to use a SQL Server Authentication and the accounts I had set up was Web ServiceApplication, type in the password, and connect, and we're connected successful. In fact right now we're connected twice. This top row in the Object Explorer is telling me I'm logged on as myself with Windows authentication and this is the view of it we'd that would log on as the SQL account and that should be a lot more restricted.
For example if I drill down onto the databases and think, well maybe I'll take a look at AdventureWorks, well I don't get to see that one. I don't get to see AdventureWorks data warehouse or data warehouse 2008 R2. I do get to expand AdventureWorksLT. I do get to expand Tables. I could even right-click and say I want to select the Top 1000 Rows because I'm allowed to read from this database. It would give me the ability to right- click and say Edit Top 200 Rows, but if I actually try to do something here like change the title, it's actually going to throw an exception here that the UPDATE permission was denied on that object.
I don't have permission to write to this database. So it wouldn't let me save this so I'm just going to hit the Escape key to jump out of having that change in there. I'm going to go back and just disconnect this part again. Disconnect as being the WebServiceApplication and just go back to being a regular administrator here. Again one of the things you want to be very careful of is when you're using SQL Server accounts and you're allowing that login ability of just a username and password, that you are using complex passwords and even complex user names as well, but if you do have to expose your connection this way, this is the way to create them.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 73701 Viewers
80 Video lessons · 129329 Viewers
52 Video lessons · 63689 Viewers
59 Video lessons · 49447 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.