Unanswered: Access SQLServer outside of the domain
I work at a site remote from the home office. Our machines are on a domain, but there is no local domain controller. We log in via cached credentials and then VPN in to the domain when we need access. Most of the time, we are not VPN'd in. I'm trying to set up a local server to run SQLServer. The server is not on a domain, nor is it a domain controller.
Because the server is not on the same domain as the machines accessing it, my understanding is that I can not use Windows authentication. I've created SQL logins and given them permission to access the database.
I'm using an Access database which I upsized to SQL server. If I am logged into the server and run the database locally, everything works as expected. (I have the local account added to the SQL logins as well.) If I run the database from my machine, however, I am unable to access the server. I get a "Cannot generate SSPI context error" and the SQL Server Dialog box pops up. I can uncheck "Use Trusted Connection" and enter the SQL login username and password. After a moment, I get the SSPI context error again. If I intentionally enter an incorrect password, I get a "Login failed" error message. If I enter the correct password, I don't see that error message and if I check the SQL Server logs, I see an entry that "Login succeeded for user 'USERNAME'. Connection: non-trusted." Despite the fact that I am successfully logging in, however, I get the SSPI context error again after a second or two and the query I'm running does not complete.
Any advice on where to go from here would be greatly appreciated.
The Cannot Generate SSPI context error usually means something wrong with Kerberos not being happy. There is a KB article that details several things that may need to happen, usually in creating a SPN (Service Principal Name) on the domain controller. I am assuming here, that the users do authenticate with a domain controller even if it is not local.
Users are logging in to a domain, yes, but we often go for long periods without connecting to the DC via a VPN. We log in through cached credentials. I'm confused as to why this would have anything to do with the SQL Server, however. (That may be my ignorance speaking, since I'm far from expert on the various security features in play.) I have the SQL Server configured for mixed authentication, and would like access to rely solely on the SQL Server username and password. I do not have access to the DC, and the SQL Server would not be able to talk to it even if I did.
I've added an SQL Server ODBC connection to my machine. I was able to add that connection and test it successfully, but am still unable to get the database to run on my machine.
After playing with the database permissions and restarting the process, I've made progress. I can now sort of access the database from my machine. What I'm seeing now is that every time the local database tries to access the server, I get an SSPI error and have to enter my SQL login username and password. For a query with a linked tables, I might have to enter my info four or five times. If I do that, the query loads and runs successfully. My machine seems to not be saving the login info but by default tries to access the SQL Server with my Windows credentials. When that fails, it gives me the error, I enter my info and the call succeeds. Obviously, having to enter my login info for every single call to the database server is not going to work. Anyone have a clue?