SQL Server 2008 18456 error and SA unable to login solution

SQL Server 2008 does not allow remote connection by default, and SA account is disabled by default. If you want to use SSMS to connect to SQL Server 2008 on remote server locally, you need to do two parts of configuration:

SQL Server 2008 does not allow remote connection by default, and SA account is disabled by default. If you want to use SSMS to connect to SQL Server 2008 on remote server locally, you need to do two parts of configuration:

1. SQL Server Management Studio Express (SSMS)

2. SQL Server Configuration Manager/SQL Server Configuration Manager (SSCM)

There are two points to note:

12005’s peripheral application configurator changed place in 2008

2. Some settings take effect and need to restart SQL server

Step 1: open SSMS, connect to database with windows identity, log in, right-click and select “properties”

Step 2: select “security” on the left and “SQL server and windows authentication mode” on the right to enable mixed login mode

Step 3: select “connect”, check “allow remote connection to this server”, and then click “OK”

Step 4: expand “security” & gt; “login” & gt; “Sa”, right-click and select “properties”

Step 5: select “general” on the left and “SQL server authentication” on the right, and set the password

Step 6: select “status”, select “enable”, and click “OK”

Step 7: right click the database and select aspect

Step 8: set “server configuration” and “remoteaccessenabled” property to “true”, and click “OK”

Step 9: so far, SSMS has been set up. Exit first, stop and restart SQL Server service, and then log in with SA. Success means that the SA account has been enabled

 

Step 10: start to configure SSCM

Start SQL Server Configuration Manager in windows menu

Select “SQL Server service” on the left and make sure that “SQL Server” and “SQL server browser” on the right are running (if not, go to “Microsoft SQL Server 2012localdb” and re open SQL configuration manager)

Step 11: the following is the English version of the interface. As shown in the figure: the TCP/IP on the right is “disabled” by default. Double click to open the settings panel and change it to “enabled”

Step 12: select the “IP addresses” tab and set the port “1433”

Step 13: as shown in the figure, change “TCP/IP” of “client protocols” to “enabled”

Step 14: after configuration, restart SQL Server 2008.

At this point should be able to use, but still want to confirm the firewall.

After SQL Server 2005/2008 Express is installed, remote connection is disabled by default. If remote access is required, manual configuration is required.

Turn on firewall settings. take SQLServr.exe (C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\ sqlservr.exe )Add to the allowed list.

More articles, related information, and so on, WeChat official account: fish science and technology information. Or click the link: http://mp.weixin.qq.com/mp/homepage?__ biz=MzU5MjY1ODg0NA==&hid=3&sn=c8b9f858ce938d785e3f799a8cb59574&scene=18#wechat_ redirect 


Read More: