Connectiing to SQL Server from an ADP

[Greg]

I know what I'm about to post is probably outside the scope of what this site
is intended for, but I have no idea where to turn at this point. I have an MS
Access 2002/XP ADP application that connects to a SQL Server Express 2005
database. I have over 1/2 dozen customers who I have installed and have up
and running just fine.

But, for some reason, I have another customer who has nothing but problems.
I have been able to install SQL Server 2005 Express on their server, which is
running Windows Server 2003 I believe. Now, several of their workstations
connect to the server just fine, but other workstations cannot connect at
all. These other workstations just say it cannot find the server, even though
I have installed Management Studio on these workstations and its able to
register with the server just fine. I would think that if Management Studio
can see the server, so shoudl my clients, since I know the connection string
is 100% correct.

I know this place is not the appropriate place to post this, but I'm at a
total loss at what to do to get my customer up and running. Since I am not a
Network Admin, I have little knowledge how to troubleshooet this problem
because I feel its a network related issus.

Is there a utility that I can have installed on the client computer that
would help diagnose why I can't find the server?

Or, is there a web-site I can direct my customer too which well help assist
them with determining why my application will not see the server?

Again, I already have several workstations at this site that connect to the
server just fine, but for some reason or another, there are other
workstations that just refuse to see the SQL Server Instance, even though the
Management Studio installation on these machines are able to register the
server

Any insite on what I must do would be appreciated. I'd just like to respond
to my customer inteligently with how he can find a resultuion. My thought at
this point is for them to have a Network Admin come on site.

Thanks,
Greg

 

[RJ]

You don't say how you are connecting to the SQL Server Express database but I
had a similar experience with a client and the solution for them was to
ensure that TCP/IP and Named Pipes were both enabled.

Check out
http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

 

[Greg]

Thanks,

I discovered that problem early on as it was why other other "working"
workstations were not initially working. Now that I've got past that its
somewhat puzzling to me. Thanks, I will take a look at your posted link.

Thanks again.

 

[RJ]

I would be very interested in the solution to your problem. Please post the
final outcome.

 

[Sylvain Lafontaine]

The presence or absence of Visual Studio has nothing to do with the
capability of ADP to find or not an instance of sql-server. There are many
possibilities here: the SQL-Server instance that you are trying to reach can
be the default or a named instance, it can use the named pipes protocotol or
the tcp-ip protocol; in the second case, it can use the default port (1433)
or another static port (1434 and up) or a dynamic port (1435 and up, 1434
will be used as the discovery service). You don't describe your
installation; so it's impossible to tell you why ADP cannot find the
instance in your case. Your problem could be anything; for example a
firewall blocking a needed port or the discovery or the browsing services
are not started and you need them because of the connection parameters that
you are using.

Beside the protocol, it's also possible that you have an authentification
problem. To debug your problem, first try with an SQL account and make sure
that you are using TCP/IP (and not the named pipes protocol) by either
creating an alias on the client (the best way to make sure that you are
using what you want to use) or by using the tcp: prefix. Here a some more
info to help you with your problem:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

http://www.datamasker.com/SSE2005_NetworkCfg.htm (connection strings for
sql providers)

 

[Greg]

Thanks for you reply. I will take a look at these sites.

Much of the information you stated was resolved during our initial
installation. As I stated, I have sereral other clients installed at this
site working just fine, plus I have the same setup at other sites. So, my
conclusion is I have a problem on the workstation side, of which I can not
figure out. I'm just a bit puzzled why I can have several workstations that
connect fine, but then at the same site, I have other workstations that won't
connect, even though an installation of Management Studio on the workstation
does connect. I did not install VStudio or Management Studio to get my
software to connect, I just installed them to see if the workstation indeed
sees the server, which it does. Because these tools can connect, I don't
understand where to look with regards to workstation related issues.

Thanks again for the information, I will take a look at these sites and see
if they offer any workstation side related solutions.

When I do finally get this problem resolved, I will post the solution.

Thanks everybody. This is a very useful resource for me and I appreciate
everyone offering to help me out as I am just getting familiar with these
technologies.

 

[David W. Fenton]

I did not install VStudio or Management Studio to get my
software to connect, I just installed them to see if the
workstation indeed sees the server, which it does. Because these
tools can connect, I don't understand where to look with regards
to workstation related issues.

It's a longshot, but have you checked what rules the firewall
software on the problematic workstations have in place? Perhaps
Access is prohibited from connecting, whereas the Management Studio
was allowed through. Have you tried it with the workstation firewall
completely disabled?

 

[Sylvain Lafontaine]

The firewall might be blocking the communication; however, VS is able to
communicate with it so I suppose that either there is no firewall or the
firewall is well configured or VS is not using the TCP/IP protocol to
communicate with it.

This problem should be relatively easy to troubleshoot by taking a look at
the installation and make sure to use the right protocol when trying to
connect from ADP. For example, by adding the prefix tcp: to make sure that
TCP is used or the prefix np: to use the named pipe protocol. In the case
of TCP, adding the port number at the end should definitely resolve any
communication problem with the server; excerpt in the case where a firewall
would block this port. However, even in the case of a firewall blocking the
port, using the same protocol as used by VS should overcome this.

Here's a very good article about this:
http://blogs.msdn.com/sql_protocols...e-the-target-machine-actively-refused-it.aspx

I should also say that in my last post, I forgot to remove the sentence «
(connection strings for
sql providers) »; as the last article
(http://www.datamasker.com/SSE2005_NetworkCfg.htm ) is not about connection
strings for sql providers.

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

The firewall might be blocking the communication; however, VS is
able to communicate with it so I suppose that either there is no
firewall or the firewall is well configured or VS is not using the
TCP/IP protocol to communicate with it.

If you're talking about a hardware firewall between the client
workstation and the server, yes.

But I was referring to a software firewall on the client workstation
that could block access based on the application requesting the
access.

This problem should be relatively easy to troubleshoot by taking a
look at the installation and make sure to use the right protocol
when trying to connect from ADP.

But with a software firewall running on the workstation that has
rules that are specific to individual applications, this could still
be correctly configured and the one application executable could
remain blocked while the other one was given free access.

[]

However, even in the case of a firewall blocking the
port, using the same protocol as used by VS should overcome this.

Only in the case of a firewall that is not aware of the application
making the connection request.

 

[Sylvain Lafontaine]

Ah yes, you're right. I don't know what I was thinking.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

The firewall might be blocking the communication; however, VS is
able to communicate with it so I suppose that either there is no
firewall or the firewall is well configured or VS is not using the
TCP/IP protocol to communicate with it.

If you're talking about a hardware firewall between the client
workstation and the server, yes.

But I was referring to a software firewall on the client workstation
that could block access based on the application requesting the
access.

This problem should be relatively easy to troubleshoot by taking a
look at the installation and make sure to use the right protocol
when trying to connect from ADP.

But with a software firewall running on the workstation that has
rules that are specific to individual applications, this could still
be correctly configured and the one application executable could
remain blocked while the other one was given free access.

[]

However, even in the case of a firewall blocking the
port, using the same protocol as used by VS should overcome this.

Only in the case of a firewall that is not aware of the application
making the connection request.

 

[Greg]

The problem has been resolved.

Here's what the solution was.

1. I use an INI file for the server connection string.
2. In my case, the Server setting was SERVERNAME\SQLEXPRESS and this worked
fine an quite a few workstations excapt a few.
3. The solution, rather than the SERVER NAME, we are using the Server's IP
Address, so its now SERVER=xx.xxx.xx.x\SQLEXPRESS and alls fine.

Greg

 

[David W. Fenton]

Here's what the solution was.

1. I use an INI file for the server connection string.
2. In my case, the Server setting was SERVERNAME\SQLEXPRESS and
this worked fine an quite a few workstations excapt a few.
3. The solution, rather than the SERVER NAME, we are using the
Server's IP Address, so its now SERVER=xx.xxx.xx.x\SQLEXPRESS and
alls fine.

That suggests that NETBios over TCP/IP was disabled on the machines
where the NETBios name didn't work but the IP address did.