Bạn đang xem: Lỗi login failed for user sa
I have two issues though:
How can I fix this?
In SSMS, right-click on the server và go to Properties & under the security page, check what server authentication looks like. Odds are it"s set lớn Windows Authentication only and needs to lớn change if you want lớn use SQL server users:
Alternative ways khổng lồ change the vps authentication mode
From Windows registry:Step 1: open the registry editor.
Step 2: Navigate to:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.10.SQLEXPRESSMSSQLServer
Step 3: Change the value of Login
Mode from 1 khổng lồ 2.
From query:
Just run
EXEC xp_instance_regwrite N"HKEY_LOCAL_MACHINE",N"SoftwareMicrosoftMSSQLServerMSSQLServer", N"Login
Mode", REG_DWORD, 2Note: changing the SQL server authentication mode requires the service to lớn be restarted lớn take effect.
Source:4 Ways to lớn Enable Mixed Mode Authentication for SQL Server
Change authentication mode with SSMS
share
Improve this answer
Follow
edited Apr 30, 2021 at 11:14
answered Apr 30, 2021 at 10:32
Ronaldo
Ronaldo
4,97411 gold badge99 silver badges4040 bronze badges
showroom a comment |
0
Right-click on the server name in SSMS và go lớn properties and under the security page.
Change the authentication from windows authentication mode khổng lồ sql server and windows authentication mode .
Don"t forget to lớn restart the SQL server Instance, as it will restart the windows services.
nội dung
Improve this answer
Follow
edited Dec 22, 2021 at 17:03
Ronaldo
4,97411 gold badge99 silver badges4040 bronze badges
answered Dec 22, 2021 at 14:21
Aman singh Parihar
Aman singh Parihar
10111 bronze badge
2
địa chỉ cửa hàng a bình luận |
Your Answer
Thanks for contributing an answer lớn Database Administrators Stack Exchange!
Please be sure khổng lồ answer the question. Provide details và share your research!But avoid …
Asking for help, clarification, or responding to lớn other answers.Making statements based on opinion; back them up with references or personal experience.To learn more, see our tips on writing great answers.
Draft saved
Draft discarded
Sign up or log in
Sign up using Google
Sign up using Facebook
Sign up using email and Password
Submit
Post as a guest
Name
email Required, but never shown
Post as a guest
Name
Required, but never shown
Post Your Answer Discard
By clicking “Post Your Answer”, you agree khổng lồ our terms of service and acknowledge that you have read & understand our privacy policy & code of conduct.
Not the answer you're looking for? Browse other questions tagged or ask your own question.
The Overflow Blog Featured on Meta
Related
14
ODBC Data Source SQL server Connection - Login Failed For User
3
sql vps authentication login failed
0
Login to SQL hệ thống 2008 R2 remotely
2
Deleted sql vps windows authentication user
0
Create a user trương mục login that cannot login from SQL vps Management Studio
8
'The computer must be trusted for delegation and the current user account must...' error when trying khổng lồ connect lớn sql vps 2008
0
Login failed for User 18456 State 5. User id works on other machines
Hot Network Questions more hot questions
Question feed
Subscribe to lớn RSS
Question feed lớn subscribe khổng lồ this RSS feed, copy và paste this URL into your RSS reader.
lang-sql
Database Administrators
Company
Stack Exchange Network
Site design / biểu tượng logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. Rev2023.6.2.43474
Your privacy
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.
You may encounter the SQL vps Error 18456 if the vps could not authenticate the connection và this can be caused by the non-availability of the administrator rights lớn the SQL vps or if the TCP/IP protocol is disabled in the SQL hệ thống settings.
The issue arises when the user tries to lớn connect khổng lồ the SQL server (local or remote) but encounters the error 18456 (with different states).
You can fix the SQL server error 18456 by trying the solutions below but before that, kiểm tra if restarting the server, client computer, and networking computers solves the issue. Moreover, make sure you are typing the correct username và password (not copy-pasting the address).
Also, kiểm tra if you are entering the correct database name (no typo in it) and make sure you have updated the configuration file accordingly. Furthermore, kiểm tra if unlocking the account (by using the query ALTER LOGIN WITH PASSWORD= UNLOCK) solves the issue. If you are seeing the errors in the SQL errors log, then make sure your SQL server is not under attack. Last but not least, make sure the server’s clock and client computer clock is correctly set.
Launch the SQL server as Administrator and Disable UAC on the Server
You may encounter the error 18456 if the SQL hệ thống does not have the elevated permissions lớn execute its operation & launching it as administrator (or disabling the UAC controls on the server) may solve the problem.
Open the SQL hệ thống as Administrator
Click Windows and type SQL server Management Studio.Now right-click on SMSS & select Run as Administrator.
Then click Yes (if UAC prompt received) and check if the SQL server is clear of the error 18456.
Launch the SQL hệ thống in a Single User Mode
Click Windows, type, and mở cửa the SQL hệ thống Configuration Manager.Now right-click on the SQL Server service (in the SQL vps Services tab) và select Properties.
Then head khổng lồ the Startup Parameters tab & in the Specify a Startup Parameter box, type:
-m
Now click on Add và apply the changes.
Then right-click on the SQL Server service và select Restart.
Now click Windows, type: SQL hệ thống Management Studio, right-click on SMSS, and select Run as Administrator.Now kiểm tra if you can connect lớn the SQL hệ thống as administrator.If so, then add the tên miền account to the SQL server và assign it the Sys
Admin role.Now go back khổng lồ the SQL vps Configuration Manager window and remove the -m parameter in the Startup Parameters tab.Then restart the SQL server service (step 3) and kiểm tra if the SQL server is working fine.If the issue persists, kiểm tra if the startup parameters or path details are properly configured. If the issue is still there, make sure your user account does have the required permissions khổng lồ the database/ reporting services, & then kiểm tra if the issue is resolved.
Enable the TCP/IP Protocol in the hệ thống Configuration Manager
The error code 18456 in the SQL hệ thống means that the hệ thống could not authenticate the connection & this can happen if the TCP/IP protocol required to lớn access the database on a network is disabled in the hệ thống Configuration Manager. In this context, enabling the TCP/IP in the SQL vps Configuration Manager may solve the problem.
Click Windows and expand Microsoft SQL Server with a year name lượt thích 2008 (you may need khổng lồ scroll a bit khổng lồ find the option).Now xuất hiện SQL hệ thống Configuration Manager và click Yes (if UAC prompt received).Then, expand SQL hệ thống Network Configuration và select Protocols for (the server/database name) in the left pane.Now, in the right pane, double-click on TCP/IP và select Yes in the dropdown of Enabled.
Then apply your changes & click Windows.
Then right-click on the SQL Server (with the server’s name) and select Restart.
Now kiểm tra if the SQL hệ thống is clear of the error 18456.
If that did not vì the trick, then make sure you are connecting to the right port of the SQL hệ thống (especially if you are using the vps in a multi-server environment).
Change the Authentication Mode of the SQL Server
The SQL server might show the error 18456 if the authentication method of the SQL server is not properly configured (e.g: you are trying khổng lồ login using SQL server authentication whereas the server is configured to lớn use the Windows authentication). In this case, changing the authentication method of the SQL server may solve the problem. Before moving on make sure the status login for the present user (for example SA) is enabled.
In the Object Explorer of Microsoft SQL server Management Studio, right-click on your server & select Properties.Now, in the left pane, select Security, và in the right pane, select SQL Server và Windows Authentication (or vice versa).
Then apply your changes and in the Object Explorer, right-click on the server.Now choose Restart và once restarted, check if you can connect khổng lồ the database without error 18456.
If you cannot log into SQL, then you may install MS power Tools và run the following in an elevated command:
psexec.exe -i -s ssms.exe
Afterward, you may use the installation account of SQL khổng lồ make the changes và also make sure the SA account is not disabled:
Enable the SA Account and Reset the tài khoản Password
If you cannot connect lớn the SQL Server, then enabling the SA account of the SQL server & resetting its password may solve the problem.
Launch Microsoft SQL server Management Studio (you may have lớn use the domain name admin account) & expand Security.Then double-click on Logins and mở cửa SA.Open the SA tài khoản in the Microsoft SQL server Management StudioNow enter a new password và confirm the password (make sure you use a strong password).Then head to lớn the Server Roles tab and make sure the following roles are selected:
Public
SysadminEnable the Public and Sysadmin server Roles for the SA Account
Now head to the Status tab và in the right pane, select Enabled (under Login).Enable the SA tài khoản in SQLThen apply your changes & click the Windows button.Now type Services and right-click on it.Then select Run as Administrator & steer to the SQL hệ thống service.Now right-click on it & select Restart.Once the service is restarted, check if the error 18456 of the SQL vps is cleared.
Create a New Login and Restart the Reporting Services
If you cannot use any account to connect khổng lồ the database, then creating a new login & restarting the reporting services may solve the problem.Launch the Microsoft SQL hệ thống Management Studio và expand its Security tab.Then expand Logins & right-click on it.Now select New Login & enter the credentials (in the login name select the computer account) if using the SQL hệ thống Authentication.Create a New Login in the SQL ServerThen make sure khổng lồ uncheck “User Must Change Password at Next Login” and select the database.Now head to lớn the Server Roles tab và select the Public role.Then, in the User Mapping tab, make sure to choose the database and select db_owner.Select db_owner for the Database in SQLNow apply your changes và click Windows.Then type Services và right-click on the result of Services. Then select Run as Administrator.Now right-click on the SQL server Reporting Service & select Restart.Restart the SQL vps Reporting Service
Then reconnect to the database and check if the SQL hệ thống is clear of the error 18456.
Xem thêm: Máy Ảnh Canon 5D Ii Giá Tốt Tháng 1, 2023, Canon 5D Mark Ii Chuẩn Bị Giảm Giá
If so, make sure that you have created a user in BUILTINadministrators, và then you can use that user to lớn manage the SQL Server. If you have restored the database from a backup, it will be better to lớn remove & re-add the users to clear any old user entries. If you want to run the SQL server as a different user, then type Microsoft SQL vps in the Windows Search, Shift+Right-click on the SQL Server, & select Run as a Different User. Last but not least, check if using Azure Data Studio with the SQL hệ thống sorts out the issue.