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)

[quoted text, click to view]
counters and view the registry on 'databaseserver' by using Windows
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

Hmm, replying to my own posting here, it seems that if I connect to
the server using named pipes protocol rather than tcp-ip sockets then
I can make use of my existing authenticated connection. Happy days.

Hey if you can't get it solved, You could run a login script to do net use at
each users login that way they wont even know it connected.

Another thing you could do is create a local account called databaseclient
on databaseserver as a local user and give them permissions to the certain
shares.

You could share a folder and you need to add that user account to the
permissions of that folder.



[quoted text, click to view]