0

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...

Arvo Bowen
  • 4,524
  • 6
  • 51
  • 109
  • So, you have two different clients trying to connect to the same DB on the same server with each client running the same application, and one works and the other doesn't? This sounds like a problem with the client that doesn't work, not with SQL Server at all. – Bacon Bits Nov 21 '15 at 01:57
  • That's EXACTLY what I was thinking... It's only logical to think that... I can't seem to find what in the world could be causing it aside from something like the SQL native client drivers... One of the working clients is using ver 2009.100.1600.01 and the one that DOES NOT work is using 2007.100.5500.00 (I tried much newer ones too)... Nothing is proving effective. I feel like I might be on the wrong track. Any ideas? – Arvo Bowen Nov 21 '15 at 02:28
  • What kind of app is the client-app? dotnet? vb6? other? – granadaCoder Nov 21 '15 at 03:35
  • Whats the nature of " keeps prompting me for a password"? Can you get to the code that does that? Behind the scenes there will be a more enlightening error message – Nick.Mc Nov 21 '15 at 08:10
  • @granadaCoder vb6 (eww I know). – Arvo Bowen Dec 01 '15 at 18:26
  • @Nick.McDermaid I do not have the actual source code. So I could not edit the connection string (or for that matter even see how it's set up and being presented). All the apps I develop have no issues connecting. It's just this old app that I did NOT develop... It does work PERFECTLY if I install SQL server 2008 r2 on the local client and connect to the local server. It's just trying to connect to the main server on the local network that gives me a problem... :/ – Arvo Bowen Dec 01 '15 at 18:27
  • More and more I think this has something to do with the fact that the server is still a 32bit machine and the client is now a 64bit machine. Before it was a 32bit client and 32bit server and that worked great. Now that the client is a 64bit machine it ONLY works when I install 64bit sql server 2008 r2 on the client (gives me 64bit server and a 64bit client). – Arvo Bowen Dec 01 '15 at 20:34
  • You're in a bit of a bind if you have an app without source code. When you run the app, does it run in 32 bit mode? (it'll have a star next to in task manager). So we really have no idea exactly how this thing is connecting. I can tell you that a 32 bit app can only see and use a 32 bit driver, but a 32 bit driver can connect to 32bit or 64 SQL Server as it connects over TCP/IP, It's not in process. I have posted a possible answer below. – Nick.Mc Dec 02 '15 at 03:19

2 Answers2

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.

granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • No doubt that would help, but I do not have the actual source code. So I could not edit the connection string (or for that matter even see how it's set up and being presented). All the apps I develop have no issues connecting. It's just this old app that I did NOT develop... It does work PERFECTLY if I install SQL server 2008 r2 on the local client and connect to the local server. It's just trying to connect to the main server on the local network that gives me a problem... :/ – Arvo Bowen Dec 01 '15 at 18:21
  • Source code for a connection string? You mean, its hard coded in? Sad if true. – granadaCoder Dec 01 '15 at 19:29
  • Well yes of course the actual connection string is hardcoded in... The user/pass/host is an ini setting but the connection string itself is hardcoded as is with most applications that connect to a database. I can't think of one application I have ever seen that has an actual connection string in a setting. – Arvo Bowen Dec 01 '15 at 20:30
  • 1
    You mean like every asp.net program ever made where it sits in web.config? – Nick.Mc Dec 02 '15 at 08:18
  • Ahh ASP... and .NET... Two things definitely not involved here. :) – Arvo Bowen Dec 02 '15 at 14:34
  • " I can't think of one application I have ever seen that has an actual connection string in a setting" ....... Then you must have been living under a rock for the last 10+ years. https://msdn.microsoft.com/en-us/library/system.configuration.configurationmanager.connectionstrings(v=vs.80).aspx – granadaCoder Dec 02 '15 at 15:07
  • Yes. I think ASP and .Net would fall under "most applications that connect to a database" – Nick.Mc Dec 03 '15 at 04:20
0

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)

Community
  • 1
  • 1
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Nice! That makes perfect sense. So yes the app is 32bit and runs as such. Also, yes I was running the 32bit of ODBC Data Source Administrator. So now that I have the 32bit version running I have A LOT more drivers to choose from. Should I use the "SQL Server" driver or the "SQL Server Native Client 10.0"? – Arvo Bowen Dec 02 '15 at 04:05
  • I tried both, it still seems as if the app is trying to connect back to the local host. Does it matter what the ODBC Data Source is named? – Arvo Bowen Dec 02 '15 at 04:11
  • Well we don't know if the app is connecting using a DSN or if it's connecting straight to the SQL Server. If it's connecting through a DSN (common in older apps) then the DSN should be system (not user) and the name needs to be exactly right. It might not be using a DSN, in which this answer has no bearing.I guess you didn't get any doco. What makes you think it's connecting to localhost? Perhaps your next step is to use Procmon (downloadable) to observe what the app does while it's running. If the app is looking in the ODBC hive if the registry it's probably looking for a DSN. – Nick.Mc Dec 02 '15 at 04:37
  • I can tell you this message: `Login failed for user 'pubclient'. Reason: Failed to open the explicitly specified database.` means your user has a default database but it doesn't ahve access (you need to tick the db in user mapping, or for simplicity, make them sysadm at the server level just for troubleshooting) – Nick.Mc Dec 02 '15 at 04:40
  • where di this "settings.ini" file come from? – Nick.Mc Dec 02 '15 at 04:41
  • As far as the Login failed message comment goes, that's exactly what I did. I don't care how unsecure this thing is right now... It's in the process of being replaced (I HATE THIS OLD 3RD PARTY APP!) ASAP. I gave it access to all the DBs and made it a master etc... Just opened it all up and that message is now gone. The setting.ini was provided with the app 10 years or so ago... Right now I'm just getting hung up on the app making a connection to the server then tries to connect to itself... It was working just fine on Win 2000, what in the world is happening on Win 7! :/ – Arvo Bowen Dec 02 '15 at 14:34
  • So the app is now working that it has sufficient access and now you want to work out why it is connecting to localhost when you specify a different sql server? No one can tell you that. It's something inside the code. I don't know how your organisation works but depending on the criticality of the app this is a big "risk". Someone needs to pay to remove the risk. – Nick.Mc Dec 02 '15 at 22:40
  • This is a very old app and the guy who developed it is long gone. This application has worked for MANY years but has always worked on Windows 2000. The issue is not "inside the code" it has something to do with the fact that using Windows 7 has changed something that it depends on. My quick solution is to just install a "new" (ahh that hurt) copy of Windows 2000 and have this machine unsupported but at least work until a new app can replace this old agonizing app! – Arvo Bowen Dec 04 '15 at 15:52