DavidOverton.com
This site is my way to share my views and general business and IT information with you about Microsoft, IT solutions for ISVs, technologists and businesses, large and small.  
David Overton's Blog » How to configure SQL Server 2005 to allow remote connections on Windows Server 2008 (Longhorn) / Windows Vista
How to configure SQL Server 2005 to allow remote connections on Windows Server 2008 (Longhorn) / Windows Vista
Thanks for sharing your feedback! If your feedback doesn't appear right away, please be patient as it may take a few minutes to publish - or longer if the blogger is moderating comments.

Just a quick one - I was playing around with SQL2005 on my Longhorn server and I could not connect from a remote machine - Ahh I thought, the firewall is in the way, but it turned out I had to do 3 or 4 things to get things working.  The Microsoft KB article that pointed to the light was How to configure SQL Server 2005 to allow remote connections which covers how to enable the firewall for the 2 programs you need and so on.

When you try to connect to an instance of Microsoft SQL Server 2005 from a remote computer, you may receive an error message. This problem may occur when you use any program to connect to SQL Server. For example, you receive the following error message when you use the SQLCMD utility to connect to SQL Server:

Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred 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.

This problem may occur when SQL Server 2005 is not configured to accept remote connections. By default, SQL Server 2005 Express Edition and SQL Server 2005 Developer Edition do not allow remote connections. To configure SQL Server 2005 to allow remote connections, complete all the following steps:

  • 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.

Well, the above is all well and good, but we also need to remember that in a Vista and Longhorn setup the "admin" accounts do not have admin control over the database, so you may well also need to run the SQL 2005 Surface Area tool that enables you to add administrators

 

I also found I had to enable network connections by going to the SQL Server Configuration Manager and enabling TCP connections on my IP v4 network.

 

 

ttfn

David

 

Technorati tags: , , ,
Posted Sun, May 27 2007 9:37 PM by David Overton
Filed under: , , , , , , ,

Comments

Jonas Lewin wrote re: How to configure SQL Server 2005 to allow remote connections on Windows Server 2008 (Longhorn) / Windows Vista
on Thu, Aug 2 2007 5:52 PM

I had the exact same problem on my longhorn (Windows 2008 Beta 3) server. The only step I could skip was the last of your steps. Probably because my client was Vista and running IPv6.

Thanks for posting the info! and I like your design on this page.

steve wrote re: How to configure SQL Server 2005 to allow remote connections on Windows Server 2008 (Longhorn) / Windows Vista
on Tue, Apr 1 2008 10:14 PM

nice beach scene. Is it hawaii.

Anyway, your idea of a tutorial is like, minimal.

Kind of like, to enable sql server remote access you have to allow remote access. I don't see how to do anything in here. Good luck with future tutorials. Don't waste to much effort in them.

Torben Rohde wrote re: How to configure SQL Server 2005 to allow remote connections on Windows Server 2008 (Longhorn) / Windows Vista
on Thu, Dec 11 2008 12:42 PM

Thanks for posting.

I was going crazy trying to get remote access to our newly installed SQL Server 2008 - but that last bit about enabling TCP/IP was just what I needed :-)

Torben Rohde, Denmark

Joe wrote re: How to configure SQL Server 2005 to allow remote connections on Windows Server 2008 (Longhorn) / Windows Vista
on Thu, Mar 5 2009 4:19 PM

Dude, you rock!!! That bit about enabling TCP connections was kind of obvious, but of course I overlooked it. Would have spent all day trying to figure it out.

kk wrote re: How to configure SQL Server 2005 to allow remote connections on Windows Server 2008 (Longhorn) / Windows Vista
on Fri, Apr 17 2009 10:00 AM

How do I do this for sql server 2008 ? Express Advanced Edition

GR wrote re: How to configure SQL Server 2005 to allow remote connections on Windows Server 2008 (Longhorn) / Windows Vista
on Wed, Aug 12 2009 3:57 PM

Could you explain step by step how to accomplish the following:

# 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.

Thanks

David Overton wrote re: How to configure SQL Server 2005 to allow remote connections on Windows Server 2008 (Longhorn) / Windows Vista
on Wed, Sep 2 2009 11:30 PM

@GR,

try clicking the link in the blog post for the answer.

thanks

David

shibin wrote re: How to configure SQL Server 2005 to allow remote connections on Windows Server 2008 (Longhorn) / Windows Vista
on Sun, Sep 4 2011 7:35 PM

how can i configure server connection using c# code?

Add a Comment

(required)
(optional)
(required)
Remember Me?

(c)David Overton 2006-23