Solved

Can't connect to SQL 2008 R 2 with OBDC within the same computer

Posted on 2013-06-16
35
535 Views
Last Modified: 2013-08-05
Just finished installing SQL 2008 R 2 in a Windows 8 Pro PC.  We can query the databases using the SQL Studio 2008 but can't seem to connect to the instance with an ODBC.

We want to connect an ODBC in the computer the SQL instance is located.

When creating the ODBC, at the user/password lever, we get the error:

e have tried using both 32 bit and 64 bit ODBC.

How can we have a working OBDC within the same PC where SQL instance is installed?
0
Comment
Question by:rayluvs
  • 18
  • 5
  • 3
  • +4
35 Comments
 

Author Comment

by:rayluvs
ID: 39251754
oops!

The error is:

---------------------------
Microsoft SQL Server Login
---------------------------
Connection failed:
SQLState: '01000'
SQL Server Error: 11001
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.

---------------------------
OK  
---------------------------
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39251865
Please make sure the SQLBrowser service is running.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39251866
Make sure also that any local firewall is disabled.
0
 

Author Comment

by:rayluvs
ID: 39252433
Sql browser is running when error occurred.

Just tried with firewall disable, and same problem.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39252500
How do you connect your ODBC ?  Is the instance well referenced?
0
 

Author Comment

by:rayluvs
ID: 39253150
What do you mean by well referenced?
0
 

Author Comment

by:rayluvs
ID: 39259562
FYI:

We don't understan by well reference, but if you mean setup with the correct parameters, yes it is.

The ODBC, with the same configuration with the exception we point it to another SQL server 2008, it works fine.  When we point the ODBC to the local PC SQL 2008 instance, it gives the error when entering the username/password and can't get pas thru this,

Please advice.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39268720
When we point the ODBC to the local PC SQL 2008 instance, it gives the error when entering the username/password and can't get pas thru this,
If you are using JET, you should know that there is no 64-bit driver for JET.
0
 

Author Comment

by:rayluvs
ID: 39269674
JET?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39270195
If you do not know what is JET then you cannot be using it and you can ignore my comment.
0
 

Author Comment

by:rayluvs
ID: 39272195
Not sure what you mean but we use ODBC to connect to SQL 2005 (our current working version).  We want to start with SQL 2008 but in our PC.  Currently our ODBC just as it is connects to a server with SQL 2008 without no problem.  The problem is when connecting locally.

We must be doing something wrong at the install in out PC.
0
 

Author Comment

by:rayluvs
ID: 39272204
Please note that to connect to SQL 2005 at a server and also to' a SQL 2008 at a server, the only thing we change is the SQL instance location; all other configuration is left as it always is.
0
 

Author Comment

by:rayluvs
ID: 39334088
For some reason my SQL 2008 R2 is not working completely in Windows 8 Pro:  cannot connect to SQL 2008 R 2 with OBDC within the same computer (we have tried connecting SQL authentication and Windows authentication and get error)

After working for while with the EE, with ODBC problem, now we get this error:

------------------------------
Connect to Server
------------------------------

Cannot connect to pc-name\SQLEXPRESS2008.

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

The initial purpose of the question was the need create a working connect to the OBDC and had this error:

---------------------------
Microsoft SQL Server Login
---------------------------
Connection failed:
SQLState: '01000'
SQL Server Error: 11001
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.

But somehow we messed it up that we can’t even connect to SQL using the SQL studio for querying.

We checked the services and the following is enabled:
Enabled
SQLBrower
SQLWriter
MSSQL$SQLEXPRESS2008

Please advice
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39338032
On the PC try 127.0.0.1\SQLEXPRESS2008 or by the real IP 10.x.x.x\SQLEXPRESS2008 and see if that works.

You may also want to restart all the services.
0
 

Author Comment

by:rayluvs
ID: 39344485
Ok didn't think of that... will try!
0
 

Author Comment

by:rayluvs
ID: 39346593
Tried all 3 recommendation and didn't work.   This is so strange.
0
Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 39348601
0
 
LVL 18

Expert Comment

by:irweazelwallis
ID: 39348608
on the SQL server configuration what do you have turned on
i.e. Named pipes, TCP etc
0
 
LVL 42

Accepted Solution

by:
Davis McCarn earned 500 total points
ID: 39348746
Verify that the SQL server name is SQLEXPRESS2008 and then try connecting to .\SQLEXPRESS2008

Also, login with ComputerName\UserName
0
 

Author Comment

by:rayluvs
ID: 39348801
GLComputing:

We have gone thru this site previously and no work.

irweazelwallis:

we have turned on TCP/IP and Shared Memory

DavisMcCarn:

".\SQLEXPRESS2008"  WORKED!!!  HOW!!!  WHY!!!


Please explain.
0
 
LVL 42

Expert Comment

by:Davis McCarn
ID: 39349154
Because it invoked a path to the root of the system.

I'm glad it worked!
0
 

Author Comment

by:rayluvs
ID: 39349181
Ok why it didn't work with the actual existing paths:

- computername\instance
- ip\instance
- either by SQL authentication or
   windows authentication.

All the above are valid path?

Why it didn't work with them? (we would like to know so if happens again try identify cause)
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39349373
Does the computer name have any special characters or spaces in it?

And are you sure you didn't typo the IP?
0
 

Author Comment

by:rayluvs
ID: 39349577
No special character and is the correct ip.
0
 
LVL 42

Expert Comment

by:Davis McCarn
ID: 39351092
Workgroup or AD?
In a workgroup with mixed O/S', you have to set one of the PC's to be the Master Browser, the rest not to be, and ensure that NetBios is enabled in TCP/IP.  Without those deliberate settings, you have a constant battle for the role.
http://technet.microsoft.com/en-us/library/cc959923.aspx

In AD, there are also many possible causes.  Reusing a computer name, any issues with DNS or DHCP are but a few.
0
 

Author Comment

by:rayluvs
ID: 39351272
Workgroup because this is a local install, we are not working connected to a server SQL instance or another pc SQL instance.  Also we may be working connected to a LAN; wired or wireless, as in also not at all connected.  This is strictly a local pc SQL instance.

As for the link, if by "MaintainServerList" you the SQL browser service, then the answer is that is  set to NO.  This is because in SQL configuration we set the SQL browser service to disable.  The purpose so it won't be displayed to other computers when connected to a LAN.  Also the local SQL install has a different port different to the standard 1433.

Please advice
0
 
LVL 42

Expert Comment

by:Davis McCarn
ID: 39351508
No; I mean the Computer Browser service which maintains the list of PC's by their NetBios name on the network.
When there are issues with who is the Master Browser, there is no good list of the NetBios names and you are seeing exactly the consequences.
0
 

Author Comment

by:rayluvs
ID: 39360887
Ok so what do I do?
0
 
LVL 42

Expert Comment

by:Davis McCarn
ID: 39361757
Either specify the hiearchy of the network using the registry values from the technet article or just keep on using the .\ which works.

http://technet.microsoft.com/en-us/library/cc959923.aspx
0
 

Author Comment

by:rayluvs
ID: 39361827
OK will check
0
 

Author Comment

by:rayluvs
ID: 39382544
Checked "\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services \Browser\Parameters" and "MaintainServerList" is set Auto.


But it's ok.  Think this question hasbeen opened enough.  The problem has been resolved and now is working which is the important thing.

Will place in another time why SQL studio doesn't work with  ".\SQLEXPRESS2008"  only with  "PC-NAME\SQLEXPRESS2008".

Thanx for all your help!
0
 

Author Closing Comment

by:rayluvs
ID: 39382579
Reviewed all entries and  DavisMcCarn ".\SQLEXPRESS2008" solved the recent connection problem and the ODBC problem.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

14 Experts available now in Live!

Get 1:1 Help Now