SQL SERVER 2008/2012/2012r2/2014 settings to turn on remote connections (SA configuration)

This article scenario applies to the Microsoft SQL Server 2008/2012/2012 r2/2014 version, hereinafter referred to as MSSQLSERVER.

MSSQL does not allow remote connections, and the SA account is disabled by default. If you want to connect MSSQLSERVER on a remote server with SSMs locally, you need to do two parts of the configuration:

1. SQL SERVER MANAGEMENT STUDIO (SSMS)

2. SQL Server Configuration Manager (SQL Server config MANAGER-SSCM)

It is important to note that if some places do not take effect, please restart SQL Server (can be found from the SSCM or from the system services), the following is the detailed steps:

STEP1. Open SSMs, connect to the database using Windows identity, log on, right-select Properties

STEP 2. Select Security, select SQL Server and Windows Authentication mode

STEP 3. Then select "Connect", tick "Allow remote connection to this server", then click "OK" button.

STEP 4. Expand Security-logins-SA, right-select Properties

STEP 5. In "General", change your own password, which is the password for your sa login.

STEP 6. In "status", Enable SA login, click "OK" button

STEP 7. Right-click Database Server, select "Facets"

STEP 8. Select "Server Configuration", locate remoteaccessenabled, set to "True"

STEP 9. Restart the SQL Server service, exit the current connection, and you should be able to log in with SA.

STEP 10. Configure SSCM, select the SQL Server service on the left, make sure that SQL Server on the right and SQL Server Brower are running, select Network Configuration, double-click TCP/IP, and ensure that the status is enabled

STEP 11. In the client also ensure that TCP/IP is enabled, the default port is 1433, you can modify, non-default port needs to be explicitly indicated in the connection string.

STEP 12. To restart the SQL Server service here, you should be ready to use it. But not yet, confirm firewall port settings (set inbound rules),

and put the SQL Server installation directory, C:\Program Files\Microsoft SQL Server\mssql10. The Sqlexpress\mssql\binn\sqlservr.exe is added to the Allowed list.

Reprint: http://www.cnblogs.com/xidu/p/4367968.html

SQL SERVER 2008/2012/2012r2/2014 settings to turn on remote connections (SA configuration)