Cached Account

Question

I have a situation where users who log into their laptops with cached credentials (not connected to the domain) need to access the SQL Express 2008 server and hosted databases.  I cannot find a way to permit this.  I get the "cannot generate SSPI context." error when trying to connect with sqlcmd (-E).  I cannot use mixed mode and in testing it I get an error saying I tried to use NT (or NTLM) authetication, so even if I could use mixed mode, it fails when trying to use a local account.  If I could use mixed mode, I can connect if the account is a SQL account and not a local machine/domain account, but I cannot hit my linked servers (local access DBs) because the account is not a Windows account and has no permissions to the files (or OLEEB provider I think).

Everything works great on the domain, but I cannot seem to get this to work when the user is operating under their cached account.  For security reasons, I cannot allow the users to log into the laptop under the administrator account which works just fine.

Does anyone know a way to do this, or will I have to abandon SQL Express 2008 as a local DB solution for these off domain situations?

Thanks,
LD

Answer 1

You have partly answered your question here . You cannot use both of the authentications (sql & windows) to access the sql server from the clients when they both are not connected not on the same domain / workgroup or anyother trusted domains.
If you have two trusted domains (sql server is in one domain and client in other domain)then at
least sql authentication can be used.

---

Thanks, Leks

Answer 2

Thanks for the reply Lekss.  Just to clarify, both the client and the sql express server are on the same laptop.  It is not a full blown SQL server on another windows box.  That being said, the SQL Express server is never in the domain beyond participating in the security architecture of the client.  That is why Windows Authentication works when I am on the network where the DC is.  But when the laptop is no longer able to authenticate to the DC, I can log into it with cached credentials but cannot connect to the databases in the Express instance with that cached account.  I can, however, open up SQL studio with that cached account and connect just fine to any database from Studio.

So I guess the basic question is, how can SQL studio connect to the databases when I launch studio with my cached account, but when I run a script that works fine when I am on the domain, it cannot connect to the SQL database under that same cached account?

LD

Answer 3

Are you seeing any errors in the Windows Application or Security Event Log?

---

Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals

Answer 4

Below are the only two places anything is logged.  I also included below these the events I can find that show me sucessfully logging on when I open SQL Management Studio.  As you can see, when I try to connect via SQLCMD, my logon fails (SSPI Context), but when I open up studio my login is successful.  All this is under the same cached account within minutes of each other.

I have googled the SSPI context and see what all they are saying, but these users need to operate while not connected to the domain, they have to use linked servers, and they need to connect to the DB with their cached credentials.

The SQLExpress Server Service runs under a local account (not system or network service) with the default settings and capabilities granted to that account during the install.  There is a chance a GPO policy might have modified the local security policy, but I would not know what policy item would be set that would cause this failure.  It is even a bit more confusing because I CAN connect to any database from SQL Studio with the cached account but CANNOT connect at all via SQLCMD.

As stated earlier, SQL Express is installed on the same laptop that the user is using (no network access).  Shared Memory and Pipes are enabled on both the SQLClient and SQLServer and SQL Server Config Manager shows this.


Command Line:

C:\Documents and Settings\<account>>sqlcmd -S .\SQLEXPRESS -E
HResult 0x80090304, Level 16, State 1
SQL Server Network Interfaces: The Local Security Authority cannot be contacted

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Cannot generate SSPI co
ntext.

C:\Documents and Settings\<Account>>

Windows System Log:


Event Type: Warning
Event Source: LSASRV
Event Category: SPNEGO (Negotiator)
Event ID: 40960
Date:  11/6/2009
Time:  5:20:34 AM
User:  N/A
Computer: <host>
Description:
The Security System detected an attempted downgrade attack for server MSSQLSvc/<host>.<domain>:sqlexpress.  The failure code from authentication protocol Kerberos was "There are currently no logon servers available to service the logon request.
 (0xc000005e)".

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


SQL Server Log when successful with cached account launching SQL Studio (Same in Windows Logs)

11/06/2009 05:26:17,Logon,Unknown,Login succeeded for user '<domain>\<account>.
Connection made using Windows authentication. [CLIENT: <local machine>]

Answer 5

I'm amazed it works in SSMS like that. I would have told you it's impossible but hey- logs don't lie.

I'm pretty confident that this comes down the authentication methods used. The failed login is obviously attempting to use Kerberos which won't work with cached credentials. I assume SSMS logs you in because it's using NTLM which is what you're cached credential is.

Can you login from SQLCMD using -S(local)\SQLExpress?

I'm not aware of any switch that would force sqlcmd to use NTLM (or why it's not falling back to it), but perhaps using the shared memory provider will help.

Answer 6

Lockwood,

I have tried that and just about every other way to connect including lcp: and np: as part of the string just in case.  In every case, I would get the error with the cached account, and at the same time running it as the local admin on the box would be successful (just verifying that my call was correct with the admin account).  I have also disabled TCP for the server so I cannot connect via the network by accident.

I have a lot of time invested in SQL Express in this app and I really do not want to move to something like SQLite to support the off-doman issue.  I would like to know how MS gets Studio to connect with the cached account as I believe that would be the answer for my issue.

Thanks for the reply,

LD