Solved

'SQL server' driver over internet ODBC not working.

Posted on 2011-09-06
10
332 Views
Last Modified: 2012-06-27
Hiya,
I have a remote server with a SQL Server 2008 database on it.

I have two desktop machines.

One machine connects to the SQL Server database over the internet using the 'SQL Server' driver. The other machine refuses to connect using the same driver but will connect using the 'SQL Server native Client' driver. However there seems to be an error produced when using the 'Native Client' driver whn accessing stored procedures.

I have checked the driver version on both machines = All good - Exactly the same.

Checked that firewall is switched off on both machines.

I am using identical connection strings.

Any ideas?
0
Comment
Question by:splanton
  • 6
  • 4
10 Comments
 
LVL 12

Expert Comment

by:NormanMaina
ID: 36487918
you can give us the errors from both types of connections -when trying to open using the sql server driver and the error when accessing stored procedures when using native client.

My feeling is authentication error for the sql driver connection and an authorization error for the native client access to sps
0
 
LVL 2

Author Comment

by:splanton
ID: 36487970
The error when connecting using the SQL Server' driver is this:-

SQL State: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server driver][DBNETLIB]ConnectionOpen
(Connect).
Connection Failed:
SQLState:'08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server driver][DBNETLIB]SQl Server Does not Exist or Access denied.

Just tried it on another machine and it doesn't work from that either. So now I have two machines that can't use the 'SQL Server' driver over the internet and one that does.

I can also now confirm that I can connect on one of the 'bad' machines to the SQL Server instance over the internet using the SQL MS with no problems at all, but still no ODBC!. ARRRRGH!
0
 
LVL 2

Author Comment

by:splanton
ID: 36487973
I can't give you the 'Native' driver error atm. Not in a position to test it.
0
 
LVL 12

Expert Comment

by:NormanMaina
ID: 36488013
Ok,try these:
1.check your connection string(if its integrated authentication,made sure the user logged in has permissions on sql server)
2.Restart all the sql server services including sql browser
3.Check no other firewalls are interfering with the connection -like anti virus firewalls.

Here is Microsoft's description of the error:
http://support.microsoft.com/kb/328306
0
 
LVL 2

Author Comment

by:splanton
ID: 36488032
OK,

Connection string = check.
Server rebooted = check.

The daft thing is that I have two desktops sat here - one connects just fine the other doesn't. Which would lead me to think that it isn't a server side issue.

I wouldn't mind but these are two relativly fresh installs of windows 7. Something is different but I have no idea how to ascertain where the issue with the connectivity is. Are there any tests I can do to help me track down the culprit?
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 12

Expert Comment

by:NormanMaina
ID: 36488083
No firewalls -anti virus on the clients?

The fresh install of windows 7,you need to make sure that the client tools are correctly installed.
0
 
LVL 2

Author Comment

by:splanton
ID: 36488113
OK, can you be more specific about client tool please?
0
 
LVL 12

Expert Comment

by:NormanMaina
ID: 36488481
the Client connectivity components

http://www.microsoft.com/download/en/details.aspx?id=23734

The other thing you need to look at is creating  a DSN for the connection
http://broadwin.com/Manual/EngMan/23.3.3_Create_an_ODBC_DSN_to_SQL_Server.htm
0
 
LVL 2

Accepted Solution

by:
splanton earned 0 total points
ID: 36582459
The problem was with the ADO driver. Even though it was set to detect the SQL Server port (1433) it requires a definitive port to be specidied to actually work!
0
 
LVL 2

Author Closing Comment

by:splanton
ID: 36708120
Tiral and error and blatent pot luck led me to use a ,1433 at the end of the connection string and behold, it now connects. Sorry for any wasted time every article I read never mentioned this syntax or the need for a port when using the default 1433 for SQL Server.
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now