SQL Server 2005: Connection Problem

[wakh]

Hi Guys,

I developed a Payment Tracking System for a client long back using Visual Studio 2005 and SQL Server 2000 which the client used for 1.5+ years. But recently they upgraded their systems and got Windows Vista Ultimate running on them. Now since SQL Server 2000 is not supported on Windows Vista I have to deploy SQL Server 2005 to be used as the database server.

Here comes the problem, which is that I have not touched the client application's code at all, because it doesn't need any modifications to run on SQL Server 2005, but when I try running the application and when it tries to connect to the database which has been restored on SQL Server 2005 Express Edition running on Windows Server 2003 Enterprise Edition the connection fails and the following error is thrown:

{"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."}

Now I am not able to figure out what is wrong, if anyone is aware of the solution to this problem then please post it here.

Some facts I would like to mention which might be useful in reaching the conclusion:

Client Application is running on Windows Vista Ultimate and SQL Server 2005 as stated above is running on Windows Server 2003 Enterprise Edition. SQL Server is set to use Windows Authentication mode only. By default use of TCP/IP protocol for connection is disabled in SQL Server 2005 Express Edition, but it has also been enabled.

ConnectionString used:

Server=192.168.1.104\SQLExpress; Database=Scale; Integrated Security=True;

 

[MattP]

Do you get the same results if you supply a username/password rather than Integrated Security?

 

[wakh]

I think supplying username and password is not required, because SQL Server is using Windows Authentication Mode only (not mixed authentication) and if I supply the credentials then I would have to change the authentication mode? However to test what I did now is copied the client application to the SQL Server machine and changed the ConnectionString to as follows to test it locally:

Server=127.0.0.1\SQLExpress; Database=Scale; Integrated Security=True;

And to my surprise it worked. I really don't understand what's the issue when connecting remotely through network? I even tried the Microsoft KB articles related to the error but none of them solve the problem? When the application is working locally, it indicates that there is nothing wrong with the code?

 

[MattP]

Initial guess would be that the surface area configuration for remote connections is set to "Local connections only" rather than "Local and remote connections".

 

[wakh]

I have enabled "Local and remote connections" under the Surface Area Configuration, and have also enabled the IP address to listen on for incoming connections under SQL Server Configuration Manager but still the application is not able to connect remotely whereas it is working locally still? Error while connecting remotely is again:

{"Login failed for user ''. The user is not associated with a trusted SQL Server connection."}

 

[wakh]

Any pointers? The problem is still not solved?

 

[MattP]

Have to tried running profiler to see what is actually going to the SQL server?

 

[wakh]

I can't use SQL Profiler because it doesn't come with the SQL Server Express Edition? I looked for it on Microsoft's website, but it's not available separately for download?

The problem I have run into is very vague, and it's the first time I have come across it, also the strange thing is it works locally, but while connecting to the same instance with the same application remotely it is throwing out exception?

 

[cjard]

I have enabled "Local and remote connections" under the Surface Area Configuration, and have also enabled the IP address to listen on for incoming connections under SQL Server Configuration Manager but still the application is not able to connect remotely whereas it is working locally still? Error while connecting remotely is again:

Did you restart SQLServer after making this change?

 

[wakh]

Yes ofcourse, a couple of times restarted but no luck. I am not able to figure out what has gone wrong? I even disabled the firewalls.

 

[cjard]

And are both these computers members of the same domain?

 

[wakh]

No, the machines are not part of any domain. The company doesn't have the domain infrastructure in place. Standard installs of operating systems only.

The older machines which have been replaced were also standard installs with no domain infrastructure.

 

[cjard]

OK, so if the two machines are entirely separate and youre using SQLS in Windows Authentication mode, what steps have you taken to ensure that the credentials presented by box A will be accepted by box B as being valid for logging into the database and carrying out queries?

 

[wakh]

I am using Integrated Security = True in the connection string, which means that the account that has been used to log on to the machine hosting the SQL Server will be used to logon to the SQL Server.

The logon mode for SQL Server is also set to Windows Authentication mode only.

 

[cjard]

Yes, but youre having problems connecting from a REMOTE machine, that doesnt have that account used to log into the server because it's not part of a domain!

ServerPC
user/pass=bob/secret

ClientPC
user/pass=joe/passpass


CLient PC comes along wanting to connect to SQLS, says "Hi, I'm username Joe", and ServerPC says "Who?"
What steps have you taken to ensure this doesnt happen?