Windows Authentication with SQL Server running on Workgroup server machine
- From: jwexqm@xxxxxxxxx
- Date: Thu, 30 Aug 2007 15:35:57 -0700
I'm currently trying to understand SQL Server Windows Authentication
ahead of writing an application that needs to connect to SQL server.
The server is a machine named 'databaseserver' that runs SQL Server
Express 2005 in Windows Authentication mode. The server is a Workgroup
computer, not on a domain and therefore has a set of local user
accounts. The account I'm interested in using is called 'databaseuser'
and its password is 'letmein'. 'databaseuser' is included in the user
group 'administrators', so SQL Server will accept connections from it.
If I log into 'databaseserver' using Terminal Services as
'databaseuser', then I can connect fine to the database using SQL
Server Management Studio Express with authentication set to Windows
Authentication and the server name to 'databaseserver\sqlexpress'.
Now, I want to make connections to 'databaseserver' from another
machine named 'databaseclient', but continue to use the 'databaseuser'
account on 'databaseserver'. It's at this point where I start having
problems.
I'm logged in on the 'databaseclient' machine as 'administrator'. If I
then run SQL Server Management Studio Express and set the
authentication to Windows Authentication and the server name to
'databaseserver\sqlexpress', it tells me...
"Login failed for user ''. The user is not associated with a trusted
SQL Server connection. (Microsoft SQL Server, Error: 18452)"
Fine, I'm logged into 'databaseclient' as 'administrator', but the
administrator password is different on 'databaseclient' from that on
'databaseserver', so it can't authenticate. And to be clear,
'databaseclient' doesn't have a local machine account named
'databaseuser' set up.
Next, I authenticate 'databaseclient' (running as administrator) to
'databaseserver' by using the following net command...
net use \\databaseserver
(and enter 'databaseuser' / 'letmein' when prompted)
counters and view the registry on 'databaseserver' by using WindowsFrom this point I can now browse network shares, view performance
Explorer or the remote machine features of perfmon.msc, or
regedit32.exe.
So, after all that preamble, what I really want to know is why, in
this authenticated state cannot I not now fire up SQL Server
Management Studio Express on 'databaseclient' and connect to
'databaseserver' using Windows Authentication with the already
authenticated 'databaseuser' account?
Once I've authenticated using net use, it seems that other remote
Windows services are happy to trust that connection, so why is any SQL
Server client being difficult about it?
I know that I could create an account named 'databaseuser' with
password 'letmein' on 'databaseclient' and then run SQL Server
Management Studio Express using that account with the Windows 'runas'
command. I've tested it and that works, but it's such a pain because
it means replicating all relevant local user accounts from
'databaseserver' on the client machines.
I really don't want to have to drop back to SQL Server Authentication
just for the SQL Server access, as I wanted to authenticate with one
authentication scheme.
Any thoughts or hints much appreciated.
James
.
- Prev by Date: This could be a connectivity issue, then again.............
- Next by Date: Re: database connection string problem
- Previous by thread: This could be a connectivity issue, then again.............
- Next by thread: Re: How to connect using a machine account?
- Index(es):
Relevant Pages
|
