April 27, 2012 at 1:04 pm
Hi all,
I am trying to setup an ODBC connection to a SQL Server,
Control Panel --> Admin Tools --> ODBC --> User DSN --> Type in Host Name --> SQL Authentication
Then I get this error,
Connection Failed:
SQL State: '01000'
SQL Server: Error: 10060
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect())
Connection Failed:
SQL State: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
Things I have tried:
1) Enabled TCP/IP and Named-Pipes
2) Did the same on the server machine hosting SQLServer, able to log in with the same host name, and same SQL Authentication.
3) I can ping the server
4) I can Remote Desktop Connection using my local computer
Can anyone help?
April 27, 2012 at 2:22 pm
Sounds like you need to enable remote connections. If value_in_use is 0 for this:
SELECT name,
value_in_use
FROM sys.configurations
WHERE name = N'remote access'
Then you'll want to log into the server again, from the local server, and run this:
EXEC sys.sp_configure
N'remote access',
N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 2:28 pm
I checked the value, it was set to 1.
April 27, 2012 at 2:36 pm
You posted in a SQL 2008 forum, so to be sure, is it a SQL 2008 or 2008R2 instance we're talking about? If so, your error message says DBNETLIB which points to you may be using a SQL 2000 driver. Do you have the SQL Native Client installed? If so, you should choose that driver when setting up the DSN, i.e. "SQL Server Native Client 10.0", not "SQL Server". The driver version (scroll right in the dialog where you pick the driver) should start with "2009.".
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 2:42 pm
Actually my SQL Server is 2012 (I guess I posted on the wrong section, should I remove this post?), I just checked my ODBC connection, it has
SQL Server 2000.85.1132.00
There is no SQL Server Native Client, what should I do next?
April 27, 2012 at 2:47 pm
No worries on the 2012 thing, now that we know, it'll be easy to get you some help.
You need the SQL 2012 Client Development Tools. You can get them here: SQL SERVER 2012 EXPRESS EDITION
I think you could go with SQL Server Express with Tools (with LocalDB, Includes the database engine and SQL Server Management Studio Express). If you do not want to install a local instance of the database engine you can get the client tools only by choosing SQL Server Management Studio Express (Tools only).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 3:12 pm
In my company, we have this offered to be downloaded.
Microsoft SQL Server Native Client 10.0 MUI
Is this enough?
April 27, 2012 at 3:14 pm
I do not think you can connect to a SQL 2012 instance with SSMS 2008.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 3:27 pm
I see you mentioned the Native Client 10.0, not SSMS...you may be able to use the SQL 2008 Native Client to create an ODBC DSN that will connect to 2012, you just may not have access to all connection-functionality that 2012 offers.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 3:59 pm
Unfortunately, when I installed the SQL Server Express with Tools, it popped up an error message saying that my computer does not meet the minimum requirement for SQL Server 2012.
So, I downloaded the SQlocalDB.msi file, it said I must install a Windows service pack that contains a newer version of the Windows Installer Service.
So..in essence...if there are no other ways, I willl have to to install 10.0?
April 27, 2012 at 4:05 pm
Bummer...the operating system requirements for SQL 2012 are the following:
> Windows Vista SP2 or above
> Windows 7 SP1 or above
> Windows Server 2008 SP2 or above
> Windows Server 2008R2 SP1 or above
If you do not have one of those, then you could try installing the SQL 2008 Native Client (10.0) that you are offered in your org but that would only give you the ability to create an ODBC DSN or connect through an app language using ADO.NET or OLE DB. The Native Client does not include the Management Studio or Data Tools IDEs.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 4:09 pm
Sigh, I will get back to you after they finish installing for me (which may take a few days). Thank you for all your help so far!
April 27, 2012 at 4:49 pm
I tried connecting with my newly installed SQL native Client 10.0, No luck 🙁
New error message though.
Connection failed:
SQLState: '08001'
SQL Server Error: 10060
[Microsoft][SQL Server Native Client 10.0]TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed ot respond.
Connection failed:
SQLState: 'HYT00'
SQL Server Error: 0
[Microsoft][SQL Server Native Client 10.0]Login timeout expired.
April 27, 2012 at 5:06 pm
it could be a lot of things...but it sounds like your SQL connection attempt is not even making it to the SQL instance.
For starters, verify:
1. You are providing the correct server name and instance name (if applicable)
2. Your server name resolves to the correct IP address
3. If you are using an instance name make sure the SQL Browser Service is running on the server
4. If you are using an instance name and the browser service is running, make sure you have network access from your machine to UDP port 1434 on the server
5. Make sure you have access from your machine to the port number of the SQL instance. You can use portqry for that. If you are not sure which port the SQL Server is listening on you can see it in the SQL Server Error Log as an entry that starts with "Server is listening on..." (that's the message logged in SQL 2008...I do not have a 2012 instance handy to verify that is what will be logged in 2012 so look for similar if not found).
Hopefully one of those will get things going for you!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 5:21 pm
1) 100% sure the instance name, I just double-checked by going to SSMS --> properties of the server --> name
For starters, verify:
2) I can remote connect with the IP I have been using.
3) SQL Browser is on
4) How do I do this?
5) How do I do this?
By the way, when I connect the through ODBC, the server drop down list is blank. Not sure if this is relevant.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply