How to Allow Remote Connection in Mssql
Problem: If the MSSql Server is not properly configured it usually gives named pipes error like "error 1326" and so on. There a...
https://www.czetsuyatech.com/2010/01/rbdms-allow-remote-connection-in-mssql.html
Problem:
If the MSSql Server is not properly configured it usually gives named pipes error like "error 1326" and so on.
There are 3 simple things that we have to set to allow remote connection on an mssql server:
1.) Make sure that the server itself accepts remote connection.
a.) Login to your SQL Server Studion Management Express, right click on the server select properties and under the Connection group make sure that the "Allow remote connections..." is checked.
2.) In the SQL Server Surface Area Configuration, under the Remote Connections select:
Local and Remote Connections->Using both TCP/IP and named pipes
3.) The default authentication mode is "Windows Authentication" to allow SQL Server Authentication, a valid user must be created. And the following key must be set in the registry:
HKLM\SOFTWARE\Microsoft\MicrosoftSQLServer\MSSQL.1\MSSQLServer\LoginMode is automatically set to 0x000002
Note: It varies from server to server.
Also in some instances you may want to backup your database (I've found it in my old documentation)
"This process is done to allow database backup because NETWORK Account will not allow us to save in path like “drive: \directory” pattern or that account might not have the privilege to save file in a specific directory chosen. The service should run under “Local System”. See “Log on As” column in the services.msc."
What to do: Under the Log On tab select the “Local System Account” and check the “Allow service to interact with desktop”. These will SQL Server as System and will prevents errors like directory permission or if the SQL Server was setup to run as a Network user.
If the MSSql Server is not properly configured it usually gives named pipes error like "error 1326" and so on.
There are 3 simple things that we have to set to allow remote connection on an mssql server:
1.) Make sure that the server itself accepts remote connection.
a.) Login to your SQL Server Studion Management Express, right click on the server select properties and under the Connection group make sure that the "Allow remote connections..." is checked.
2.) In the SQL Server Surface Area Configuration, under the Remote Connections select:
Local and Remote Connections->Using both TCP/IP and named pipes
3.) The default authentication mode is "Windows Authentication" to allow SQL Server Authentication, a valid user must be created. And the following key must be set in the registry:
HKLM\SOFTWARE\Microsoft\Microsoft
Note: It varies from server to server.
Also in some instances you may want to backup your database (I've found it in my old documentation)
"This process is done to allow database backup because NETWORK Account will not allow us to save in path like “drive: \directory” pattern or that account might not have the privilege to save file in a specific directory chosen. The service should run under “Local System”. See “Log on As” column in the services.msc."
What to do: Under the Log On tab select the “Local System Account” and check the “Allow service to interact with desktop”. These will SQL Server as System and will prevents errors like directory permission or if the SQL Server was setup to run as a Network user.
Finally to connect to a remote server using the management studio, in the server name you need to input like this: tcp:ipAddress\instanceName,port
Example: tcp:192.168.0.116\sqlexpress08r2,1433
Note: Also make sure that you set IPAll.port=1433 in the SQL Server Configuration Manager.
Post a Comment