Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

'SQL server' driver over internet ODBC not working.

Posted on 2011-09-06
10
Medium Priority
?
340 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

879 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