I have installed SQL Server and Management Studio on new machine and facing issues while connecting to SQL Server through remote machines on my local area network. After thorough searching and experimenting I have got resolved the error. Following are the steps(not necessary all) to resolve the issue.
TCP/IP's state should be Enabled. (can be Enabled/Disabled by right click on Protocol Name)
SQL Server should be running
Go to All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager. Select node SQL Server 2005 Services, under SQL Server Configuration Manager parent node. SQL Server's state should be Running. SQL Server Browser service should also be Running (you can Start/Stop by right click on service name)Enable TCP/IP in SQL Server Configuration
Go to All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager. Select node Protocols for SQLServer(your server's instance name) , under SQL Server 2005 Network Configuration.TCP/IP's state should be Enabled. (can be Enabled/Disabled by right click on Protocol Name)
App Exception for SQL Server TCP Port in Windows Firewall
Go to Conctrol Panel > Windows Firewall > Exceptions (tab). Click button Add Port, enter name and port number 1433. Make sure your entered name is displaying in Programs and Services section, and should be checked while pressing OK button on the tab.App Exception for sqlbrowser.exe in Windows Firewall
In Firewall window > Exceptions tab, click on Add Program. Locate the sqlbrowser.exe by Browse button. Click OK. At my system, it is located at C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe.App Exception for sqlservr.exe in Windows Firewall
Similarly add sqlservr.exe in Firewall Exceptions. At my system, it is located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe.Enable Remote Connection
Right click on SQL Server main node in Management Studio, and go to Properties. Select Connections page from Select a Page section. Allow remote connections to this server should be checked.References
- http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/
- http://msdn.microsoft.com/en-us/library/cc646023.aspx
Sure, the section labeled "Enable TCP/IP in SQL Server Configuration" also points the same. (It seems these both options are same and provided on different UIs for easier usability.)
ReplyDeleteSuperb blog , it’s a really informative blog for all user who can facing windows 7 problem, Our services provide complete solution of your windows 7 realted problem.please go through this link .
ReplyDeletewindows firewall error 1068 windows 7
Thank you
Aalia lyon
windows 8 application development more attractive and have become liked due to it graphic designed. All windows applications have developed in .NET framework due to its security.
ReplyDeleteThanks Muhammad for sharing this article with us. I have found one informative article that shows how to fix SQL Server Error 26: http://www.sqlserverlogexplorer.com/fix-error-code-26/
ReplyDeleteYes John, thats good article. It explains the firewall option with screenshots in windows 7+.
Delete