We're having trouble connecting to our SQL Server (2008 R2) via SSPI.
Our team has recently moved from a domain-based setup to a decentralised workgroup-based setup. Each developer has access to a VPN which, in turn gives them access to a database server running SQL Server 2008 R2.
We've previously used Windows authentication, logging in from our domain-joined PCs with our DOMAIN\user.name usernames, having added accounts to the remote SQL server (on a workgroup) with the identical usernames and passwords (but not the domain) to a Windows security group, and granting that group permissions on the SQL server.
However, since switching from our domain, we have not been able to connect using SSPI.
When trying to access the database using Windows Authentication over TCP/IP, we get the following error message:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)
Specifying a SQL Server login works as expected, but using SSPI fails. Checking the event log on the SQL Server side, we see the following:
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. [CLIENT: 192.168.xxx.xxx].
Our managed hosting provider has set the workgroup name to a number – we'll pretend that the workgroup is called 12345678 (the actual name is our account number). I've already tried changing my local workgroup to the same number, but this hasn't solved the issue.
Many of the solutions suggested by other users reference a tool called setspn, and using that tool to list and find SPNs in use for our accounts – using this doesn't show anything out of the ordinary.
We're working around the limitation by using:
runas /netonly /user:REMOTEPCNAME\user.name "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
Are we stuck with having to use this way to authenticate?
Eleven years ago, I worked around a similar problem problem by mapping a drive letter on my workstation to the remote machine and providing my credentials on the other machine. For you, that would look something like this (from a CMD or PSH shell):
Clearly, that uses an "admin share" that you might not have access to. That wasn't a problem for me because I was a member of the Administrators group on that box. If you don't have admin access, you might have to get a public share set up (by someone with enough rights) and use that, which I believe may work even though I haven't tried it.
This trick was a pretty common thing to do back then, when domains were rarer and AD was a new-ish thing. Even though it isn't a very common thing to do anymore, I don't recall hearing that Microsoft had dropped or broken this workaround.
I've never seen SPNs used in any situation that did not have a full-blown Active Directory set up. IIRC, SPNs need Kerberos and Kerberos needs a domain. I suspect that the SPN lead is a red herring. Good luck.