Set up basic Windows Authentication to connect to SQL Server 2008 from a small, trusted network
- by Margaret
I'm guessing that this is documented somewhere on Microsoft's site, but thus far I haven't found it.
I'm trying to set up a Windows Server 2008 box to have SQL Server 2008 with Windows Authentication (Mixed Mode, actually, but anyway) for work. We have a number of client machines that will need access to the databases, and I would like to keep configuration as simple as feasible.
Here's what I've done so far:
Install SQL Server 2008 selecting Mixed Mode
Create a new 'Standard' (rather than Administrator) Windows login entitled "UserLogin" (with intent to use it as the access account)
Create an SQL Server Login for Server\UserLogin and assign it 'Windows Authentication'
Log in as UserLogin, check that I'm able to connect to SQL Server using WIndows Authentication, then log out again
Start on the first client (Windows XPSP2, SQL Server 2005):
Run C:\WINDOWS\system32\rundll32.exe keymgr.dll, KRShowKeyMgr
Click "Add", enter the server name in the box, Server\UserLogin in the Username, and UserLogin's password in the Password field. Click "Ok" then "Close"
Attempt to access SQL Server 2005 using Windows authentication.
Succeed. Confetti!
Start on the second client (Windows 7, SQL Server 2008):
Run C:\WINDOWS\system32\rundll32.exe keymgr.dll, KRShowKeyMgr
Click "Add", enter the server name in the box, Server\UserLogin in the Username, and UserLogin's password in the Password field. Click "Ok" then "Close"
Attempt to access SQL Server 2008 using Windows authentication.
Receive an error "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication"
Assume that this translates to "You can't have two connections from the same account" (Yes, I know that doesn't make sense, but I'm a bit like that)
Go back to the server, create a second Windows account, give it SQL Server rights.
Go back to the second client, create a new passkey for the second login, try logging in again. Continue to receive the same error.
Is this all overly complex and there's an easy way to do what I'm trying to accomplish? Or am I missing some ultra-obvious step that would make everything behave as desired? Most of the stuff that's coming up when I try to Google seems to be along the lines of "My ASP.NET application isn't working!", which obviously isn't all that much use.
