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?