Can't connect to SQL Server 2008 R2 with new windows 7 64bit machine

Question

I have been trying to figure this out for days now... I have this old application that I have not had to mess with in years and this old windows 2000 (32bit) machine went out on me. So I ended up replacing that machine with a new Windows 7 (64bit) machine.

Everything seems to be working just fine with the exception of one thing. When I try to connect to the database with the app it keeps prompting me for a password as if it's not able to connect or see the server is a SQL Server in the first place!

I have tested the connection with the ODBC Data Source Administrator and that worked perfectly "TESTS COMPLETED SUCCESSFULLY!". But when I try to connect using my app it just times out and asks for me to enter the user/pass again. :/

I had another old machine (windows 2000 32bit) up and running and it was accessing the db just fine so I know the server is setup right with open ports and no firewall etc..

I just can't seem to find a reason this client will not connect! Any ideas?

---

Update #1:

It seems that when turning on logging auditing in the SQL Server Management Studio I was able to see the following two log entries (in the following order)...

TEST #1

Login succeeded for user 'pubclient'. Connection made using SQL Server authentication.

Login failed for user 'pubclient'. Reason: Failed to open the explicitly specified database.

So when I try this on the local client machine with the temp install of SQL Server 2008 r2 it works perfectly fine. Same exact settings just a different host in the settings ini file used!

TEST #2

Login succeeded for user 'pubclient'. Connection made using SQL Server authentication.

Starting up database 'iri'.

Login succeeded for user 'pubclient'. Connection made using SQL Server authentication.

I don't have to specify a database on the local machine (client->client), so why do I when trying to connect from client->server?

---

UPDATE #2

So from what I can tell at this point is if I use a bogus host (one that can not be pinged) this application will ask me for a user/pass (because it can not make a connection to anything). If I use a host that does respond to a ping it seems to be happy and proceed. By proceed I mean it will connect to the correct remote server (as shown in UPDATE #1 on TEST #2) but before it does the second login attempt (Login succeeded for user 'pubclient'. Connection made using SQL Server authentication.) it decides to try and connect to the LOCAL SQL server I set up as a test on the client machine!!! I do not have the client host listed ANYWHERE in the settings (the client machine name was just made up so it's not hard coded). For some reason now that I have upgraded the client PC to Windows 7 64bit (from Windows 2000 32bit) it wants to continue trying to connect to the local machine instead of the remote host like it does initially!! WHAT THE HECK! I have no clue why it wants to connect to the localhost instead but this does NOT happen on Windows 2000...

Answer 1

I would suggest trying a "more exact" connection-string.

https://www.connectionstrings.com/sqlconnection/connect-via-an-ip-address/

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;
Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

or

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;
Initial Catalog=myDataBase;Trusted_Connection=True;

This connection string has an IP address and Port, specifies the Network Library (tcp/ip) (Do a search for dbmssocn and you'll find the others, but dbmssocn is the first one to try). The first one above is for sql server authentication...the second one is for integrated-security.

I mention this because I had issues with a client that would not work with named-pipes, but worked with tcp.

It's not a great chance this is it, but its worth a shot.

Answer 2

I see you are using ODBC. You need to make sure you set up a 32 bit ODBC. To do that you must use ODBCAD32.exe in the wow64 directory. The default ODBC admin on the start menu goes to the 64 bit, and your app (being 32 bit) will not see this. So it might be a crappy app hiding the fact that it can't find a DSN with a login error message.

Take a look at this https://superuser.com/questions/419832/how-can-i-open-the-32-bit-odbc-data-source-administrator-in-windows-7-64-bit and please confirm that your app is running in 32 bit mode (has a star next to it in task manager)