I needed to create a test environment for one of our applications that uses SQL Server as its data store. Our sysadmin cloned the existing production server to create the test server.
I can connect to SQL Server on the test server using Windows Authentication, but not the production server (I can connect to production using a SQL login). I could connect to the production server using Windows Authentication before it was cloned.
Here is the error message:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)
What can I do to connect using Windows Authentication again?
SQL Server 2008 R2 version: 10.50.2550.0
Some more info…
- All of our servers are virtual (using VMware)
- This same situation has happened with another production SQL Server. Again, we made a clone of production for a test environment. As with this case, I'm able to connect to that SQL Server using Windows Auth.
Sean brings up a good point about invalid SPNs, but what I don't understand is why SQL Auth. would work if it was cloned improperly?
Best Answer
If the machine SID was not changed Active Directory will only allow the most recently booted machine to authenticate users.
Cloning a server and not giving it a new SID will definitely cause it to conflict with the current PROD server. I would advise doing this sort of thing in an environment separate from production.