The full error message is SQL Server Error 18452, "Login failed. The login is from an untrusted domain and cannot be used with Windows Authentication"
I have just got this error message again on a Windows 7 Workstation, that I am using SQL Server Management Studio on.
It reminded me that I wanted to blog about the resolution to this and also add how to solve this for Named Instances as well.
Firstly some background to the issue:
I have a Windows 7 Workstation that I use SQL Server Management Studio to connect to SQL Servers in both a Production (PROD) and TEST Domains. However, I log on to the Workstation using a Local Windows Account. I have to do this as the PROD and Test Domains are predominantly XP, and the Group Policy that was developed for XP is different to that used in Vista and Windows 7. Currently, we are not able to operate in a mixed mode domain as a result the current roaming profiles we have DO NOT WORK for Window 7. This is a major problem because it means whenever you log off you have to re-assign Outlook Exchange connections and you lose all your internet favourites etc.
So I have 3 Accounts all with the same Username
LOCAL\MyUser
PROD\MyUser
TEST\MyUser
I log onto my Windows WorkStation using LOCAL\MyUser and then want to connect seamlessly to both PROD and TEST SQL Servers using SQL Management Studio using Windows Authentication (Not SQL Authentication). I can either do one of three things:
1) I can attempt to use the Shift Key and then Right Click on SQL Management Studio and 'Run as Different User' and use either PROD or TEST Accounts
However, I cannot then intermix in SSMS Local SQL Server and different Domain SQL Servers - I am stuck with just those SQL Servers in the Domain I logged in on.....
2) I can attempt to use a Line Command as detailed by Jason Follas here:
http://www.jasonfollas.com/wiki/(X(1)S(zucey4jfr2sr4maocepgrn55))/Default.aspx?Page=SQL%20Management%20Studio%20on%20Vista&AspxAutoDetectCookieSupport=1
However, again I am limited to using just those SQL Servers in the one Domain, and secondly also I am prompted for the password each time as /savecred does not work with /netonly at the same time.
3) Finally I can attempt to use Windows Credential Manager to store the various Domain Credentials for me. However, when I did this (the first time) it did not work and I still had the SQL Server Error 18452 Message. I could not find out the answer until Google popped up the following link and Arsine's answer (thanks again Arsine!)
http://social.msdn.microsoft.com/Forums/hu-HU/sqlsecurity/thread/c05a90e4-cb16-46f6-9072-37083c65696d
I have to create a Windows Credential and add the SQL Server Port (1443) to the end of the Internet or Network Address in the form :<SQL_Port_Number>
So to seamlessly connect to SQL Servers in Any Domain from Windows 7 in SQL Management Studio you need to
• Use Control Panel, User Accounts, Manage Your Credentials, Add a Windows Credential
• Add all the SQL Servers you want by Fully Qualified Name but you must include the PORT Number as say :1433
• Example:
• ANYSERVER.ANYCOM.TEST:1433
• Username: TEST\AnyUser
• Password: TEST Password
• Press Add Button
Note: Each SQL Server will have to be added separately
Note: If there are any Password changes then all references in Windows Credentials will need to be updated
This way you can control all SQL Servers in ALL Domains seamlessly from one instance of SQL Server Management Studio
I would like to add two additional points to Arsine's excellent posting
A) I find that it is best to Fully Qualified Name the ServerName in Windows Credentials and SQL Management Studio rather than just the Servername . So use ANYSERVER.ANYCOM.TEST rather than just ANYSERVER
B) If you have a NAMED INSTANCE of SQL Server you must create multiple entries in Windows Credentials, one using the default SQL Server Port 1433 and one for each Port allocated for the Named Instance such as 2335 so use
ANYSERVER.ANYCOM.TEST:1433
as well as
ANYSERVER.ANYCOM.TEST:2335
Note: Of course this will have to be defined as ANYSERVER.ANYCOM.TEST\NamedInstanceName in SQL Management Studio
To find out the Port that SQL is running on (there are many methods) I suggest you read the link below by Colin Stasiuk which contains by far the most comprehensive solutions I can find. I didn't like hunting throug Registry Entries and looking at the SQL Server Logs was by far the quickest solution (noting the 'possible gotcha' detailed )
http://sqlserverpedia.com/blog/sql-server-bloggers/what-tcp-port-is-sql-server-running-under/
Finally I know I could have used SQL Server Authentication without a problem to achive the same thing but I did not want to go through this less secure method.
PS. To avoid having to use the Shift Key when Right-Clicking for 'Run As Different User" in Windows 7:
-
Just go to registry run regedit.exe, search for “runasuser”, then under it you’ll find empty value REG_SZ named “Extended”.
-
Delete every “Extended” (there should be around 5 of them
-
Result is that there is no need to press shift key anymore, the option will ALWAYS appear when you Right-Click