I use windows 7 ultimate. I can login with windows authentication mode but SQL authentication with user sa doesn’t work. sa user is enabled. So what do I do?
RBT
23k21 gold badges155 silver badges231 bronze badges
asked Aug 27, 2010 at 11:16
Go to Start > Programs > Microsoft SQL Server > Enterprise Manager
Right-click the SQL Server instance name > Select Properties from the context menu > Select Security node in left navigation bar
Under Authentication section, select SQL Server and Windows Authentication
Note: The server must be stopped and re-started before this will take effect
Error 18452 (not associated with a trusted sql server connection)
RBT
23k21 gold badges155 silver badges231 bronze badges
answered Aug 27, 2010 at 11:18
KashifKashif
13.8k17 gold badges63 silver badges98 bronze badges
Login with windows authentication mode and fist of all make sure that the sa authentication is enabled in the server, I am using SQL Server Management Studio, so I will show you how to do this there.
Right click on the server and click on Properties.
Now go to the Security section and select the option SQL Server and Windows Authentication mode
Once that is done, click OK. And then enable the sa login.
Go to your server, click on Security and then Logins, right click on sa and then click on Properties.
Now go tot Status and then select Enabled under Login. Then, click OK.
Now we can restart the SQLExpress, or the SQL you are using. Go to Services and Select the SQL Server and then click on Restart. Now open the SQL Server Management Studio and you should be able to login as sa user.
answered Aug 29, 2019 at 9:58
Sibeesh VenuSibeesh Venu
16.9k11 gold badges91 silver badges136 bronze badges
-
Click on Start menu > Programs > Microsoft Sql Server > Configuration Tools
-
Select Sql Server Surface Area Configuration.
-
Now click on Surface Area configuration for services and connections
-
On the left pane of pop up window click on Remote Connections and Select Local and Remote connections radio button.
-
Select Using both TCP/IP and named pipes radio button.
-
click on apply and ok.
Now when try to connect to sql server using sql username and password u’ll get the error mentioned below
Cannot connect to SQLEXPRESS.
ADDITIONAL INFORMATION:
Login failed for user ‘username’. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
ation
To fix this error follow steps mentioned below
-
connect to sql server using window authentication.
-
Now right click on your server name at the top in left pane and select properties.
-
Click on security and select sql server and windows authentication mode radio button.
-
Click on OK.
-
restart sql server servive by right clicking on server name and select restart.
Now your problem should be fixed and u’ll be able to connect using sql server username and password.
Have fun.
Ateev Gupta
answered Feb 2, 2011 at 12:03
- First make sure
sais enabled - Change the authontication mode to mixed mode (Window and SQL authentication)
- Stop your SQL Server
- Restart your SQL Server
RBT
23k21 gold badges155 silver badges231 bronze badges
answered Aug 24, 2013 at 10:23
- Go to services.msc from run prompt.
- Restart the services of SQL server(MSSQLSERVER)
- Restart the services of SQL server(SQLEXPRESS)
RBT
23k21 gold badges155 silver badges231 bronze badges
answered Jul 18, 2016 at 10:55
sivasiva
1151 silver badge4 bronze badges
I faced the very same error when I was trying to connect to my SQL Server 2014 instance using sa user using SQL Server Management Studio (SSMS). I was facing this error even when security settings for sa user was all good and SQL authentication mode was enabled on the SQL Server instance.
Finally, the issue turned out to be that Named Pipes protocol was disabled. Here is how you can enable it:
Open SQL Server Configuration Manager application from start menu. Now, enable Named Pipes protocol for both Client Protocols and Protocols for <SQL Server Instance Name> nodes as shown in the snapshot below:
Note: Make sure you restart the SQL Server instance after making changes.
P.S. I’m not very sure but there is a possibility that the Named Pipes enabling was required under only one of the two nodes that I’ve advised. So you can try it one after the other to reach to a more precise solution.
answered Mar 15, 2018 at 12:43
RBTRBT
23k21 gold badges155 silver badges231 bronze badges
I was stuck in same problem for many hours. I tried everything found on internet.
At last, I figured out a surprising solution : I had missed SQLEXPRESS part of the Server name: MY-COMPUTER-NAMESQLEXPRESS
I hope this helps someone who is stuck in similar kind of problem.
answered Jun 29, 2020 at 7:39
In my case I just closed MSSMS and restarted SQL SERVER from services.
answered Jul 4, 2022 at 11:54
Ali SheikhpourAli Sheikhpour
9,7555 gold badges37 silver badges80 bronze badges
- Remove From My Forums
-
Question
-
Using sql2005 on a Novell network…
When attempting to log into Mgt Studio using windows authentication I am getting the error: Error 18452 (not associated with a trusted sql server connection) .
I added my domainuserName in sql with no problems and added the account to the sysadmin server role. However, I noticed my credentials on my pc are not listed as domainuserName but rather ComputerNameUserName. I think this is what is causing the problem but I am not sure. Can anyone lend a hand?
I’d appreciate any help,
Thanks.
Scott
Answers
-
I think Laurentiu is right, I have found an article from IBM website:
«….Solution
To resolve this issue, follow the instructions to set User Authentication.
SQL Server 2000:Go to Start > Programs > Microsoft SQL Server > Enterprise Manager
Right-click the Server name, select Properties > Security
Under Authentication, select SQL Server and Windows
The server must be stopped and re-started before this will take effectSQL Server 2005:
Go to Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio
Right-click the Server name, select Properties > Security
Under Server Authentication, select SQL Server and Windows Authentication Mode
The server must be stopped and re-started before this will take effect…»http://www-1.ibm.com/support/docview.wss?uid=swg21119906
It has solved my problem, so I hope it could be helpful for you too.
-
Can you please post the exact error message from the SQL Server errorlog including the number and state of the error?
Also, to get additional information, please connect the SQL Server Profiler and monitor the Audit Login and Audit Login Failed events in the Security category. Check to see what are the credentials that appear for the login attempt.
You’ll probably not be able to login anyway if your credentials are different from the ones you specified in SQL Server, but I would expect to see a different error in that case.
Thanks
Laurentiu -
I had the same message when trying to connect to a SQL 2005 instance through Windows authentication.
The client was written is VB 2005 and used OLEDB client access (not the SQL native client!)
Switching the server to both SQL and Windows authentication did not solve the problem.
I checked different parameters for the connection string and, finally, found the next one that works:
‘Connection information to connect to the database server to retrieve data
‘This is connecting to SQL server through OLEDB
Dim ConnectionString As String = «»
ConnectionString = «Provider=SQLNCLI;»
ConnectionString += «Server=YourServerName;Database=YourDatabaseName;»
ConnectionString += «Integrated Security=SSPI;»
Important to note that Integrated Security=True; does not work.
Horia
-
Alexander, Welcome to MSDN
In Sql Server 2005 go to Management studio > In object explorer right click on the server name >> go to properties >> Click on the security tab >> Choose Sql Server and Windows Authentication mode and restart the Sql services.
— Deepak
Recently a user has reported error 18452 while connecting to database server. Full description of this error is given below:
Error: 18452, Severity: 14, State: 1.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
You can see the screenshot of the error 18452 in below image.
Error 18452 – Root Cause
Reason behind error 18452 is because of wrong security authentication mode configuration. SQL Server is set to accept only windows logins to connect to database instance.
As we know SQL Server uses two authentication modes to accept database connections. One is Windows Authentication mode and another one is SQL Server and Windows Authentication mode. We also call it Mixed Authentication mode.
Sometimes, SQL Server authentication mode is set to SQL Server and Windows Authentication mode to accept SQL as well as windows connections but still you will face this issue. That might be because you try to connect to a server that has Always on Availability Group configuration or database mirroring configuration.
Suppose you have AOAG configuration and you are connecting to database using primary replica name not listener name using a login for which default database is set as availability database. If failover got happen during your activity or you are connecting to secondary replica using a login for which default database is set as availability database then also you will get this error 18452 along with some SSPI context error that I have given below.
Error: 17806, Severity: 20, State: 14.
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. The logon attempt failed
SSPI handshake errors comes because of Kerberos failure, which would most likely be related to non-existent SPN or bad SPN for SQL Server.
Solution
To fix this issue first we need to check the authentication mode set for your SQL Server Instance. If your SQL Server instance is running with windows authentication mode then you must change it to Mixed mode so that windows and SQL both type of logins can be authenticated.
To check the configured authentication mode for your SQL Server Instance, we need to launch SQL Server Instance property window. Right click at the instance node and click at the properties option as shown in below screenshot.
Once you will click at Properties option, you will be getting below SQL Server instance property window. Click at Security option from left side pane. You can see current Server Authentication configuration is set to Windows Authentication mode in below screenshot.
Now we will change it to SQL Server and Windows Authentication mode. Select the radio button for SQL Server and Windows Authentication mode option and click ok button to proceed. You can see that I have selected above mode to apply the changes.
Restart SQL Server services to apply the changes.
If your SQL Server Instance is already set with SQL Server and Windows Authentication mode then you should fix this issue in separate way. As I described above that one of the probable reason to get this error 18452 is because you might use AOAG replica server name to connect to the database with the login for which default database is set as the AOAG availability database.
If failover will happen then you would not be able to access the database because it will become secondary. Error 18452 will be generated along with SSPI handshake errors because same database is set as default database for your login that has become inaccessible now because of acting secondary database in AOAG. Failover will not happen for you because you are using replica server name to make database connection.
Possible solution to fix this issue is failback the AOAG to your earlier primary replica or you should use AOAG Listener name to make database connection. Also, to avoid this error during failover you should set default database for your login to master rather than availability database.
I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.
Read More:
- Fix Error 18456: Login failed for user “User_Name”
- Fix Error 4064: Cannot open user default database. Login failed
- Error 53: Could not open a connection on SQL Server
- AOAG Listener Error 19471: The handle is Invalid
- Author
- Recent Posts
I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Summary
Article Name
Fix Error 18452: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
Description
Recently we got a login issue where one of the SQL user was not able to connect to database instance and was getting error 18452. Full error description of this error is given below: Error: 18452, Severity: 14, State: 1.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
Today I created a new virtual machine to do some SharePoint 2010 testing. First installed Windows 2008 R2, then added and configured the DNS and Active Directory roles. Installed Sql Server 2008 R2. Started the install of SharePoint 2010. Untill this point everything went very smooth. But during the installation you have to give the database name where the SharePoint databases will be created. And since I didn’t like the hostname, I wanted a dns entry for it with a better name. Should be simple, right?
Started the dns server management console and added the entry sql.mb.local pointing to my fixed ip. Used the new name in the SharePoint installation screen and got login failures.
Hmm, what is going on here? Is Sql service running? Did I used the wrong ip? Those all checked out fine. So I started the SQL Server Management Studio. I could connect using the hostname and ip addresses but not through the dns name. If I did that I got a nice little box saying: «Login Failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)»
Searched for the errors on internet and found some blog postings about enabling Mixed Mode authentication, instead of Windows only authentication. But I was sure it must also work with just Windows authentication. Next step was to look in the eventviewer and there was an event 17806 in the Application Log saying «SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed.» and in the Security Log there was an event 4625 saying «An Error occured during Logon«.
I also tried it with a CNAME record as alias for the host name and that worked perfectly. I also checked if there were SPN’s registered to the sql service account. None were registered. Also checked for MSSQLSvc spn in the domain, also none found. But by this time I was convinced it had to be some kind of Kerberos issue or NTLM reflection protection. But in both cases I am still puzzled why using a HOST A record doesn’t work, while the hostname or CNAME does work. But I tried the standard fixes for both problems and they both work (Technically both solutions work for the NTLM reflection protection problem):
Solution 1 — Register a SPN for the SQL Service
Execute the following command to register a SPN
SetSpn -A MSSQLSvc/sql.mb.local:1433 MBsql_service
(Where MSSQLSvc is the service class for SQL Server, sql.mb.local is my dns entry, 1433 is the port my sql instance is listening on and MBsql_service is the account sql server is running as)
Solution 2 — Disable NTLM reflection protection
Create a new DWORD with the name DisableLoopbackCheck and value 1 in the registry key HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlLsa and restart the computer.
This will disable NTLM reflection protection on the whole machine, which opens it for Man In The Middle attacks, but on my test machine this is ok 😉
For production machine reference these KB articles for other ways to disable the protection: Users experience authentication issues… and MS08-068: Vulnerability in SMB could allow remote code execution
Posted
11-11-2009 8:33 PM
by
Michel Barneveld