?
Solved

'SQL server' driver over internet ODBC not working.

Posted on 2011-09-06
10
Medium Priority
?
342 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
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
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

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

600 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