Please visit my new Web Site https://coderstechzone.com
- Sql server does not allow remote connections.
- SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified.
- An error has occured while establishing a connection to the server. When connecting to SQL Server 2005,this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider:Named Pipes Provider,error:40-Could not open connection to SQL Server))Server does not exist or access denied.
- Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
- Turn on the SQL Server Browser service.
- Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.
Enable remote connection:
If you use Sql Server Management studio then:
open.
Connect to the remote server.Right click on the server instance.Select properties.
Click Connections.Check to allow remote connections to this server.
If you use Express or Developer edition then follow the below steps:
Click Start -->> Programs -->> Microsoft SQL Server 2005 -->>
Configuration Tools -->> SQL Server Surface Area Configuration.
Now click Surface Area Configuration for Services and Connections.
Now expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
Restart MSSQLSERVER service from
Start -->> Control Panel -->> Administrative Tools -->>
Service -->> Select MSSQL Server -->> Restart Service
Enable Sql Server Browser service:
One thing noted that this may increase security risk. Microsoft recommend that you use this process only if you really require this process.To enable the SQL Server Browser service, follow these steps.
Click Start -->> Programs -->> Microsoft SQL Server 2005 -->>
Configuration Tools -->> SQL Server Surface Area Configuration
Now click Surface Area Configuration for Services and Connection
Select SQL Server Browser -->> Select Startup type Automatic -->> Apply
Create exception in windows firewall for SQL Server:
If you are running a firewall on the computer that is running SQL Server 2005, external connections to SQL Server 2005 will be blocked unless SQL Server 2005 and the SQL Server Browser service can communicate through the firewall. You must create an exception for each instance of SQL Server 2005 that you want to accept remote connections and an exception for the SQL Server Browser service.
Open Start -->> Control Panel -->> Windows Firewall
Select Exceptions tab -->> Add Program -->> Browse
Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK.
Note: path may be different depending upon your instalattion.
You can get more details from :
http://support.microsoft.com/kb/914277
http://www.linglom.com/2007/08/31/enable-remote-connection-to-sql-server-2005-express/
3 comments:
Can I enable these settings though script , since I need to do this without telling user of my project...and I need to use sql express...
Is there any way
Thank you
Sandesh Daddi
http://sanshark.com
mail : sandesh[at]sanshark[dot]com
HI Sandesh,
Yes it is possible by modifying the registry values using DOS/TSQL script. For more details you can visit the below link:
http://sql-articles.com/blogs/enable-disable-remote-connections-via-dos-or-t-sql/
I WOULD BE DELIGHTED TO HEAR FROM YOU