Using cached credentials to connect to SQL 2005 across a domain boundary

Question

Ever since moving to Vista some time ago on my development machine, connecting to SQL Servers in our DMZ active directory domain from client tools like SSMS has not worked like it used to. In XP, as long as I had authenticated in some way on the server (for example directing Explorer to \server.dmzdomain\c$ and entering valid creds into the login prompt), SSMS would use those cached credentials to connect.

However since switching to Vista, when trying to connect SSMS to a server in the DMZ domain I get the message Login failed for user ''. The user is not associated with a trusted SQL Server connection. If I change the connection options to use Named Pipes instead of the default TCP/IP, my cached credentials are sent and everything works fine. This is the case whether Windows Firewall is off or on, and connections to servers in our internal domain (the same domain my dev PC is in) work fine over TCP/IP or named pipes.

I don't mind too much using named pipes for these connections as a workaround, but it seems like TCP/IP is the recommended connection method and I don't like not understanding why it's not working as I'd expect. Any ideas?

Answer 1

Have you tried running SSMS in elevated mode, and do you have the latest SP installed on the client?

Answer 2

I would assume that this is because Vista runs most applications in isolation from either other.

I would recommend that you either set the DMZ username and password to match the internal domain username and password, or use named pipes to connect.

Answer 3

"Login Failed for user ' ', the user is not associated with a trusted SQL Server connection".

In this scenario, client may make tcp connetion, plus, running under local admin or non-admin machine account, no matter SPN is registered or not, the client credential is obviously not recognized by SQL Server.

The workaround here is:

Create the same account as the one on the client machine with same password on the target SQL Server machine, and grant appropriate permission to the account.

Let's explain in more detail:

When you create the same NT account (let's call it usr1) on both workstations, you essentially connect and impersonate the local account of the connecting station. I.e when you connect from station1 to station2, you're being authenticated via the station2's account. So, if you set the startup account for SQL Server (let's assume it's running on station2) to be station2's usr1, when you connect to SQL from station1 with station1's usr1 login, SQL will authenticate you as station2's usr1.

Now, within SQL, you can definitely access station1's resources. Though, how much access will depend on station1's usr1 permission.

So far, SQL only deal with an user who is part of the sysadmin role within SQL Server. To allow other users (non-sysamdin) access to network resources, you will have to set the proxy account. Take a look at the article for additional info. taken from http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx