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;

 

[wakh]

You don't need to know the account for logging into the SQL Server remotely when integrated security is used. The account detail is inserted and used by the machine itself when the packet reaches it. The server machine will recognise the account details that have been used to logon for that particular session?

 

[cjard]

So a SQL Server in Integrated Security/Windows Auth mode will let any Tom, Dick or Harry access it, because it just magically inserts an administrator login and password when the remote user attempts to connect?

I'm curious to know what is secure about this security?

 

[JohnH]

I'm curious to know what is secure about this security?

*sarcasm detected*

You don't need to know the account for logging into the SQL Server remotely when integrated security is used.

Yes, you somehow do because you need to configure network and server accounts before this will happen. If the remote account is not recognised as a windows account at server machine it will also not allow it to access the SQL server. Integrated security is simpler when user is already authenticated in network with windows logon credentials, because then user has strong authentication and doesn't need to go through yet another login system with the SQL server.

 

[wakh]

That's how integrated security in SQL Server works. What is secure about this security method is a different issue. The problem now is unable to connect remotely. I am wondering if I am the only one who ran into this problem?

 

[wakh]

JohnH: That's what is happening in my case. Administrator is logged on to the machine when I am trying to connect to it, which means that the connection from my application will use that (Administrator) account while trying to logon to SQL Server? But still it is failing.

On a side note I would like to mention that this is how the previous setup was done, there is no change except the hardware and software upgrade of machines.

 

[cjard]

There's no change, other than it's a fresh bit of hardware and software?

I've had this same brush for 15 years.. It's had 3 new heads and 5 new handles..


No, but seriously. Do you really expect that a machine running SQL Server will use the credentials of the logged-on user to allow any arriving connection to authenticate? What's the point in that?

How to: Access SQL Server Using Windows Integrated Security

If your application runs on a Windows-based intranet, you might be able to use Windows integrated authentication for database access. Integrated security uses the current Windows identity established on the operating system thread to access the SQL Server database. You can then map the Windows identity to a SQL Server database and permissions.

To connect to SQL Server using Windows integrated authentication, you must identify the Windows identity under which your ASP.NET application is running. You must also be sure that the identity has been granted access to the SQL Server database. This topic includes a code example that displays the current Windows identity of the ASP.NET application.

Connecting to SQL Server
If SQL Server is on a different computer than the Web server, the Windows identity must be able to flow across the network to the remote instance of SQL Server. (Windows networks that have been configured appropriately with Kerberos authentication are able to do this.) However, depending on the settings in the identity configuration element, the Windows identity established on the operating system thread for ASP.NET applications may not be able to flow properly to the remote SQL Server.

I suggest you switch off Integrated Security if youre not on a domain, and use traditional username/password. I cannot guarantee that setting up an account on both machines, that has the same username and password will a) work, b) be sensible, and short of setting up a domain or kerberos challenge I really don't see how you can get your credentials to be recognised universally

 

[wakh]

What I meant by "There's no change, other than it's a fresh bit of hardware and software" is that the setup is still the same, i.e is the same as it was before the upgrade.

I don't expect it, that's how the integrated security works. That's how Microsoft designed it, and the point of it is that when you are authenticating onto a machine running SQL Server, then you are authorized or trusted to access the database too, because otherwise you are not supposed to be logging on that machine in the first place. Apart from everything I am using this method from quite long, its not the first time and it still works on SQL Server 2000, only having problem in this new version of SQL Server. The article above is about the the authentication of the ASP.NET application, which is configured in the IIS itself. It doesn't apply to the standard windows application or in particular to this scenario.

As for setting up same usernames/passwords on both machines, it will not work because every account gets a unique SID, on which permissions are assigned, not on usernames and passwords.

 

[cjard]

Well, you clearly know so much more than me about windows authentication, so I'll take my opinion that your SQL Server doesnt believe you've authenticated at all, and go away. Hope you get it sorted.

 

[wakh]

Thanks mate, I will have to try experimenting some more with it and see where does it lead.

 

[drew4663]

I know this is an old thread but maybe you are still struggling or someone is still needing the answer.

1. Did you find a solution?

2. Have you tried this.....

Go to Start->All Programs->SQL Server Express->Configuration Tools-> and open the management console. I don't have it in front of me but it's where you are able to stop and start the service. Anyway, expand the server and make sure TCP/IP is enabled, then right click and choose properties. Make sure that the IP address is changed from 127.0.0.1 to your 192.168.1.XXX. Hope this helps.

 

[wakh]

I carried out all those steps that time, but for some reason it was still not working. So what I did is hosted SQL Server and application both on the same machine and used "localhost" to connect, which worked.