Client cannot connect to SQL Server instance
-
We've just set up an SBS Standard server, installed MSDE SP4 and a new instance. This is running on the server successfully however users cannot access the instance - error stating the SQL server does not exist or access denied. I have checked that the protocols - TCP/IP and Named Pipes - have been enabled but still can't connect. Any help would be much appreciated. Thanks
-
What method of connection are you using... ADO, ODBC, etc...
Is port 1433 TCP and/or port 1434 UDP blocked by firewalls?
Jason
-
-
Hi Jason - We're using ODBC and at the moment the firewall is switched off - both ports are open.
Cheers
Paul
-
If you can connect locally by creating an ODBC Data source that points to the local machine, then it looks on the surface like network.
If you are connecting using machine name (MyServer) try putting in the IP Address of the SQL Server. If this works then there is an issue with name resolution, check your DNS Servers or Hosts file.
If you are connecting using the IP Address and it still isn't working, try to ping and tracert to determine if network traffic is failing somewhere.
When setting up your ODBC Data source on the client machines, check the box to dynamically determine port.
-
-
Thanks for that. Access to the instance from the SBS server itself is fine. Changing the name of the server to the IP address failed. Ping and Tracert to server name and IP address both work correctly. "Dynamically determine port" is already checked in the data source.
Error message = Attempting Connection
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not exist or access denied.
TEST FAILED!
-
What version of MDAC are the client machines running. If you don't have the MDAC Component Checker, download it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=8f0a8df6-4a21-4b43-bf53-14332ef092c9&displaylang=en&Hash=SSP4SMB
Here are some troubleshooting steps for the error you are getting: http://support.microsoft.com/default.aspx?scid=kb;en-us;827422
Try these, hopefully they will solve your issue.
Hope this helps,
Jason
-
-
Paul,
I haven't seen your connection string and you might want to take the time to post it (without a login name or password, of course!) but, if the name of the server has special characters such as "-" or "\", you will need to encapsulate the server name in quotes when connecting.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks Jeff. Currently the server name is simply – Server – and the string to access the datasource is server\fininv. I have tried double and single quotes around server\fininv and I’ve tried double and single quotes around server only and I always get the same error - [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not exist or access denied.
TEST FAILED!
If I leave the quotes off, the error changes to - [Microsoft][ODBC SQL Server Driver][TCP/IP]Specified server not found.
TEST FAILED!
Here’s a slightly rambling, but hopefully clarifying, account of what I’ve done.
On the server - MSDE with SP4. Edited the setup.ini file and added and instance name, an SA password and left Mode at SQL. Ran setup. Right clicked on SQL Server Services Manager and selected the instance just created (Fininv) and clicked Refresh. SQL Server appeared in the “Services” box and the instance was active.
Started ODBC manager and set up a Fininv datasource – name=Fininv, server = server\fininv. Clicked on finish, clicked on test account – test successful. Installed and configured Argos Financial Software. Ran AFS software successfully.
On Workstation – Installed MSDE with SP4. Created a datasource - name=Fininv, server = server\fininv. Clicked on finish, clicked on test account – Failed - [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not exist or access denied. Used ping and tracert to verify connectivity – all OK.
As Per Jason’s suggestion, checked MDAC – updated to 2.8 from 2.6 and retested – same problem. A number of permutations later and still no access
Paul
-
Shoot... I'm a little outside my league on this one... I'll tell you this, though... if you run this from a command prompt...
OSQL -L
... and it doesn't list the server name, then the Windows Server isn't seeing the SQL Server as part of your domain (or so say the guys at work). I'm not sure how to fix that and the guys at work were too busy to show me.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
From the server, are there any differences in how you've set up the ODBC data source...
Check to see that TCP/IP is enabled for the named instance, it should be since it works locally, if not this is the problem. Check the port for the named instance and specify this port instead of dynamically determine port...
-
-
Hey guys, thanks for all the responses. Seem to have solved the issue now.
SBS by default adds two instances – MSSQL\SBSMONITORING and MSSQL\SHAREPOINT and starts the SQL server. To be able to get this financial software to run, a new instance needs to be added. To do this I was installing the MSDE supplied with the AFS software. I found out this was not installing correctly and was conflicting with the default instances. Also – even though TCP/IP and Named Pipes were enabled through SQL software, the server wasn’t actually “Listening” for them. Only “Shared Memory” was listed in the log file.
To resolve those issues I rebooted the server, stopped all SQL services, Stopped the instances listed above, downloaded SQLExpress and installed that, rebooted the server and created a new datasource – Bingo all worked well. Tested access from a workstation in our workshop – no problems, everything worked and the AFS software ran well. Finalised the server install, packed everything up, delivered it to the client and set it up. Joined the workstations to the domain and tried to set up new data sources – failed.
Installed SQLExpress on the workstations and tried again – all OK.
So there you have it. A right pain in the Butt. Thanks again
Paul
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply