Solved

'SQL server' driver over internet ODBC not working.

Posted on 2011-09-06
10
336 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

A safe way to clean winsxs folder from your windows server 2008 R2 editions
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
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…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

738 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