Problem with ODBC 32 bit

We are configuring an ODBC 32 bit using "%windir%\SysWOW64\odbcad32.exe" under a Windows 8 Pro computer.

The problem we are having that after testing it gives us this message:
Attempting connection
[Microsoft][SQL Server Native Client 11.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 
[Microsoft][SQL Server Native Client 11.0]Login timeout expired
[Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

TESTS FAILED!

Open in new window



Here ia pix prior and after testing:
ODBCerror.jpg
We checked the remote connection and server name; both are ok.

When we run the SQL2008 studio it run well but creating the ODBC, it fails.

please advice on how we can find a solution for this.

Thank you in advance.
rayluvsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
Note:
We are creating the odbc for a connecting GP2013 to the sql 2008 (both on same PC)
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
Could you ensure that SQL Browser service is running.
rayluvsAuthor Commented:
You are correct, just checked and its off.

Ok will proceed to Start and retry create the connection.

Thanx!
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

rayluvsAuthor Commented:
Didn't work; same problem.
rayluvsAuthor Commented:
Sorry for the delay; been super sick.

Unfortunately, the recommendation didnt work; still same problem creating the odbc for in SQL2008.sql 2008
Kelvin SparksCommented:
This is connecting to SQL Express. Have you allowed connections to database and setup the ports correctly in the SQL Server Config utlity?

It is reporting a access error - usually generated if port 1433 isn't open, sql not configured to use that port or configured to allow connections from elsewhere


Kelvin
rayluvsAuthor Commented:
Hi,

Yes, it is SQL Express 2008.

by "SQL Server Config utility", do you mean "SQL Server Configuration Manager"?
DcpKingCommented:
Try doing a search for just "*odbc*.*" inyour Windows directory tree. You should find two entries - one for 32-bit and one for the 64-bit version. Try using the other one (the one you're not using now).

Hth

Mike
rayluvsAuthor Commented:
We have to use the 32 bit version, the odbcad32.exe file (this is because the apps GP2013 requires this ODBC.  The other doesn't work with this apps.
Kelvin SparksCommented:
Yes, the SQL Server Configuration Manager. Have you enabled the TCPIP service in network settings, What port is being used (should be 1433). You'll have to do all that SQL Server Management Studio doesn't use these, hence it can connect.

Many applications need 32 bit, so you're on the right track there.


Kelvin
Kelvin SparksCommented:
Are you connecting on the same machine, or is your application on another. If a different machine, you'll need to open the Windows firewall to port 1433 as well.


K
rayluvsAuthor Commented:
Yes, on both enabled the TCPIP service in network settings & using 1433 port.

Also yes, we are connecting to the same machine.

sql
larryhSr. Software EngineerCommented:
Are you using the same SQL login credentials in ODBC that you do with SSMS?  I see that you are not using Windows authentication (Integrated Security) so that means to me you have to specify a username/password when you test your connection.  Make sure those credentials are the same as you use when you connect with SSMS.
rayluvsAuthor Commented:
Yes we use the same login credentials.  We have to use "sa" user for the odbc connection to work with the apps.

We have checked those credential and when we go to SSM it goes in with no problem, but when creating or testing the odbc32 it doesn't.
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
Could you provide the following:

1.  Provide the port on which the SQL server is running (go to SQL Server Network Configuration)
2.  What is the instance name (I believe the Windows Server name is XAU and the SQL server name is XAU\SQLEXPRESS2008)
3.  Provide the NetStat information for SQL Server (i.e. "run netstat -aon -b" and paste the sections that start with [sqlservr.exe])
4.  Paste the output for the following command:  netsh advfirewall show currentprofile
joinaunionCommented:
Is there anything in this tutorial that you have missed?
http://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/
rayluvsAuthor Commented:
Answers to questions:

Provide the port on which the SQL server is running (go to SQL Server Network Configuration)
1433

Open in new window


What is the instance name (I believe the Windows Server name is XAU and the SQL server name is XAU\SQLEXPRESS2008)
Correct

Open in new window


Provide the NetStat information for SQL Server (i.e. "run netstat -aon -b" and paste the sections that start with [sqlservr.exe])
[sqlservr.exe]
  TCP    0.0.0.0:1536           0.0.0.0:0              LISTENING       896
 [sqlservr.exe]
  TCP    [::]:1536              [::]:0                 LISTENING       896

Open in new window

We noticed that it says 1536, is that the IP? What is 1536?

Paste the output for the following command:  netsh advfirewall show currentprofile
Private Profile Settings: 
----------------------------------------------------------------------
State                                 ON
Firewall Policy                       BlockInbound,AllowOutbound
LocalFirewallRules                    N/A (GPO-store only)
LocalConSecRules                      N/A (GPO-store only)
InboundUserNotification               Enable
RemoteManagement                      Disable
UnicastResponseToMulticast            Enable

Logging:
LogAllowedConnections                 Disable
LogDroppedConnections                 Disable
FileName                              %systemroot%\system32\LogFiles\Firewall\pfirewall.log
MaxFileSize                           4096

Ok.

Open in new window


Also checked the links provided in the last entry and it seems ok.


Please advice what are we missing or doing wrong.

Thank you guys very much!
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
Your SQL server is listening on port 1536 and not 1433 which means that you need to have port 1536 opened in order to connect to your SQL server remotely.  You could also confirm it or change it to 1433 by going to SQL Server Configuration Manager, expand SQL Server Network Configuration, double click on protocols for SQLExpress2008, double click on TCP/IP in the right side window and then change the TCP port for "IPALL" to 1433.  Below is a screen capture of what it looks like:

sql-set-port.png
rayluvsAuthor Commented:
For some reason it just doesn't work.

We can run scripts, manage databases using the Studio (and we have 2 sql instances) with no problem.

Yet when creating the ODBC, it keeps giving me the error "failed" as stated at the beginning of the question.

One thing we noticed was that even though we have 2 sql instances, when creating the odbc, it displays only one.

WE HAVE TWO (2) SQL INSTANCES:
sqlsinatnaces
TESTED ON ALL DRIVERS AND OBDC32 ONLY DISPLAY JUST ONE (1) INSTANCE:
1instance
rayluvsAuthor Commented:
Just FYI:

this is the odbc32 file and location we use for our GP apps to connect:
odbc32
larryhSr. Software EngineerCommented:
I think you have the wrong EXE.  This is from Microsoft...
https://support.microsoft.com/en-us/kb/942976

A 64-bit version of the Microsoft Windows operating system includes the following versions of the Microsoft Open Database Connectivity (ODBC) Data Source Administrator tool (Odbcad32.exe):
•The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
•The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.
Based on what you said, you are actually running the 64-bit version of the ODBC Admin tool.

I know it sounds backwards but there is a kind of logic to it.  The SysWoW64 folder has to do with what is known as "Windows on Windows" or the 32-bit software running on a 64-bit platform.  For compatibility they make the default System32 folder for the OS native software (in your case 64-bit).

Try the SysWow64 folder and see if you don't connect.
joinaunionCommented:
In Sql server services is the SQL Server Browser stopped or started?
rayluvsAuthor Commented:
Started (but also stopped just in case)
larryhSr. Software EngineerCommented:
It looks like your GP apps are using the 64-bit version of the ODBC Admin tool instead of the 32-bit version.
rayluvsAuthor Commented:
No.  The GP documentation specifies to use 32 bit.
larryhSr. Software EngineerCommented:
Yes, but your screen shot shows the folder C:\Windows\System32 which is the 64-bit version.  Either you're using the wrong EXE or your screen shot was of the wrong folder.
rayluvsAuthor Commented:
The GP recommendation states to use odbc 32 bit when in a 64 bit OS, it suggest to use the odbcad32.exe file; located in C:\Windows\System32 folder.  

Reading your entry, gave us the doubt so we did a search odbcad32.exe file, and that's the only place found.
joinaunionCommented:
With it started do you still have the same issues?
Kelvin SparksCommented:
THe 32 bit version of odbcad32.exe  is usually found in the folder C:\Windows\SysWOW64

Kelvin
rayluvsAuthor Commented:
Sorry about that you are correct, made a mistake.  We are using SysWOW64 folder version as stated in the questionL

"We are configuring an ODBC 32 bit using "%windir%\SysWOW64\odbcad32.exe" under a Windows 8 Pro computer."

And yes, we still have the same issues.
(we are googling to see what else we have missed)
Vitor MontalvãoMSSQL Senior EngineerCommented:
In the SQL Server machine open SQL Server Configuration Manager, go to SQL Server Network Configuration (not the 32bits option) and check if Named Pipes for the MSSQL instance that you want is enabled. If not, then enable it.
Now go to TCP/IP properties and check in the IP Addresses tab which one is set to the desired MSSQL instance.
rayluvsAuthor Commented:
The  Named Pipes was not enabled; just did it.

When you say "check in the IP Addresses tab which one is set to the desired MSSQL instance", what do you mean? Where do we look at? (see pix below)

ip2
Vitor MontalvãoMSSQL Senior EngineerCommented:
The  Named Pipes was not enabled; just did it.
Good.

When you say "check in the IP Addresses tab which one is set to the desired MSSQL instance", what do you mean? Where do we look at?
Can you see the Dynamic Ports? It has a value and that means every time SQL Server service is restarted it will use a different port and that's why you can't open the firewall only on port 1433. Best way for dynamic ports is to open the firewall to a program instead of a port. In this case you should allow sqlsrv.exe to pass through your firewall.
Kelvin SparksCommented:
I would delete the dynamic ports entry for IP All, and set TCP Port to 1433 - unless you have another instance using 1433.


Kelvin
rayluvsAuthor Commented:
Understood on "means every time SQL Server service is restarted it will use a different port", but for placing the rule in our firewall, we have searched for the file "sqlsrv.exe" but can't seemed to find (and we searched the entire drive).  Maybe in our  version MSSQL2008 the actual physical filename is different?  What windows option is related to the file your are recommending to set so we can do a "Open File Location" and identify it.

As for " delete the dynamic ports", we were going delete the dynamic ports entry for the section "IPAll" as requested, but the image is from december 11 and we already have changed it with EE help in this question.  Below is the actual configure; is it correct?

ip3
Kelvin SparksCommented:
Yes, I also add 1433 to each of the settings above for good measure. You must then ensure there is a port opened in the windows firewall for port 1433.


Kelvin
rayluvsAuthor Commented:
So you say for every IP, IP1, IP10, IP10 all thru IPAll, change:

TCP Dynamic Port to "blank"
and TCP Port to 1433

Is this correct, all the IP's section?
Kelvin SparksCommented:
Is what I do.
Kelvin SparksCommented:
Don't forget to restart the SQL Server instance after you have done this to ensure that all changes are picked up.


Kelvin
rayluvsAuthor Commented:
Ok, let's give it a try!
Vitor MontalvãoMSSQL Senior EngineerCommented:
I would delete the dynamic ports entry for IP All, and set TCP Port to 1433 - unless you have another instance using 1433.
He has another instance but I don't know which port is using.


So you say for every IP, IP1, IP10, IP10 all thru IPAll, change:
Not to all. You'll only need to do that where's the Dynamic Port has an value.


we have searched for the file "sqlsrv.exe" but can't seemed to find
Sorry, there's a typo there. The correct name is sqlservr.exe. Here's the MSDN article where you can find the reference to add a program exception to the firewall.
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
I am beginning to think that there is only one SQL server running the second instance might be an alias to the first one.  Could you validate as to what databases are users are on the two instances.  If they are the same, what happens if you add a user to one of the instances, is it reflected on the second one?
rayluvsAuthor Commented:
There are 2 instances and 2 database folders.  Nevertheless, we'll run your recommendation and get back to you.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Should be 2 instances. You can confirm that by checking which processes are running in the server. You should find two sqlservr.exe processes.
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
Vitor, if you scroll above, there is only instance that was provided when I asked him to run netstat -aon -b
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can see two in comment #41371662
Vitor MontalvãoMSSQL Senior EngineerCommented:
And also in comment #41367192
rayluvsAuthor Commented:
Ok, FYI on our SQL:

We have 2 instances and both running and both with different root/db folders (see pix below).

sqinstanc2
Instance 1: SQLEXPRESS2008
Root:              c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008\MSSQL
DB Location: c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008\MSSQL\DATA

Instance 1: SQLEXPRESS2008_B
Root:              c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008_B\MSSQL
DB Location: c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008_B\MSSQL\DATA


For the validation requested, what do you mean or what should we do to validate your request of  "what happens if you add a user to one of the instances, is it reflected on the second one?"
rayluvsAuthor Commented:
Guys, we were thinking; maybe we should just delete all SQL instances, clean everything up no reference of SQL and instal just on (1) clean SQL.

What you guys think?
Kelvin SparksCommented:
Can you advise what was the thinking that lead to 2 instances.

Secondly, what ports is the other instance using. That then usually needs the SQL browser to resolve, something I'm not too sure is supported in SQL Express.

Unless there's very good reasons to have two instances, I'd be going with one.


Kelvin
rayluvsAuthor Commented:
We installed a second instance for the GP apps. (we didn't want GP to use the current SQL instance).

the first instance is 1433, the second is  1565.

What about what we said about erasing all and reinstall?
Kelvin SparksCommented:
Sometimes, doing that will solve your problems - I did solve one like this by doing just that - but you cannot be sure that it will do that.


Kelvin
rayluvsAuthor Commented:
We are going to go thru all the observations and recommendations in this question again, and other link results we have found, to see if we missed anything.  If all reviewer is correct and still problem persist we will post here.
Vitor MontalvãoMSSQL Senior EngineerCommented:
We installed a second instance for the GP apps. (we didn't want GP to use the current SQL instance).
This is a good reason to have a 2nd instance. Another reason is if a problem occurs with an instance won't affect the databases in the other instance.
So, if you manage to go for one instance you don't need to uninstall both but the 2nd one only. Then attach the databases from 2nd instance in the 1st instance after uninstall it.
rayluvsAuthor Commented:
worked!!!  

What we did:

Uninstall Microsoft SQL Server 2008 R2
(used as reference "How to uninstall an instance of SQL Server 2008" - https://support.microsoft.com/en-us/kb/955499)
Restart the computer
Went into Registry and deleted all SQL references
Restart the computer again
Installed fresh Microsoft SQL Server 2008 R2

Thanx for all your help!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
We have also marked our final enter as a solution and mark it as "Best Solution" since it solved the solution (we would have preferred identify the problem and fixed it instead of total reinstall but the process we went rhtu also helped understand more this process)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.