Unable to Connect to a SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Unable to Connect to a SQL Server

Author

Topic

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2010-02-08 : 19:04:55

Hi there, I have the following situation and have run out of idea's

The error message the end user gets is the following

[DBNETLIB][ConnectionOpen](Connect()).]SQL Server does not exist or access is denied.

This is when trying to connect to the SQL Server through MS Excel.

So what I have checked

SQL Server properties

Remote connections allowed
Not a CLuster

SQL Server Configuration Manager

TCP/IP = enabled

SQL Services

SQL Browser running
SQL Agent Not running

The client uses SQL Express 2008 sp1
the PC that cannnot connect is on a peer to peer network

Any ideas appreciated!

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2010-02-08 : 20:03:55

I forgot to mention that creating a new Data Source (ODBC) though admin tools could not find the SQL Server Express instance.

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2010-02-08 : 21:25:31

OK I noticed the following

SQL Server Configuration Manager – TCP/IP for the protocols was not enabled
SQL Browser – This was disabled (see option B in step 2 below)
SQL Agent – This was disabled.

I have asked the IT people of SQL to recheck option B for Step 2
I think the SQL Agent is a non issue.
So they will check the following

STEP 1: Enabling TCP/IP

First we must tell SQL Server Express to listen on TCP/IP, to do this
perform the following steps:

1. Launch the SQL Server Configuration Manager from the "Microsoft SQL
Server 2005 CTP" Program menu
2. Click on the "Protocols for SQLEXPRESS" node,
3. Right click on "TCP/IP" in the list of Protocols and choose, "Enable"
STEP 2: To Browse or not to Browse

Next, we have to determine if we want the SQL Browser service to be running
or not. The benefit of having this service run is that users connecting
remotely do not have to specify the port in the connection string. Note: It
is a security best practice to not run the SQLBrowser service as it reduces
the attack surface area by eliminating the need to listen on an udp port.

OPTION A: If you want to always specify a TCP port when connecting (Not
using SQL Browser service) perform the following steps else skip these
steps:

1. Launch the SQL Server Configuration Manager from the "Microsoft SQL
Server 2005 CTP" Program menu

2. Click on the "Protocols for SQLEXPRESS" node

3. Click on the "TCP/IP" child node

4. You will notice an entry on the right panel for "IPAll", right click
on this and select, "Properties"

5. Clear out the value for "TCP Dynamic Ports"

6. Give a TcpPort number to use when making remote connections, for
purposes of this example lets choose, "2301"

At this point you should restart the SQL Server Express service. At this
point you will be able to connect remotely to SQL Express. A way I like to
check the connection is my using SQLCMD from a remote machine and connecting
like this:

SQLCMD -E -S YourServer\SQLEXPRESS,2301

The "," in the server name tells SQCMD it's a port.

So you've tried this and still get an error. Take a look at Step 3, this
should address the remaining issue.

OPTION B: If you want to use SQL Browser service perform these steps:

Note:
You will need to make this registry key change if you are using the April
CTP or earlier versions:

To enable sqlbrowser service to listen on the port 1434, the following
registry key must be set to 1

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\SQL
Browser\Ssrplistener

Next, restart the sqlbrowser service.

1. Start the SQL Browser Service

STEP 3: Firewall..?

At this point you should be able to remotely connect. If you still
can't chances are you have a firewall configured on the computer where SQL
Express is running. The instructions below are for Windows XP SP2's
firewall settings.

To enable the firewall to allow SQL Server Express traffic:

1. Launch the Windows Firewall configuration tool from the control
panel.

2. Click the Exceptions Tab

3. Click the "Add Programs." button and select "sqlservr.exe" from the
location where you install SQL Server Express

You should be able to remotely connect. Note, you can get more restrictive
by just specifying the port number that will be allowed (used best when
configured with Option A).

Note: If you chose to use the SQL Browser service, you must also add
sqlbrowser service executable to the exception list as it listens on udp
port 1434.

Anything else I should consider?

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-02-09 : 10:52:37

I think the only thing you have not mentioned is the authentication model.

I assume the SQL Server instance is set to mixed adn that there is a SQL Server account for the user in question?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources