SQL Server 2005: Connection Problem - Page 3

cjard · #21 (**permalink**) 12-15-2008, 1:28 PM

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?

Quote:

Originally Posted by http://msdn.microsoft.com/en-us/library/bsz5788z.aspx

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 · #22 (**permalink**) 12-19-2008, 5:45 AM

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 · #23 (**permalink**) 12-19-2008, 9:44 AM

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 · #24 (**permalink**) 12-21-2008, 11:05 AM

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

drew4663 · #25 (**permalink**) 05-01-2009, 2:24 PM

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 · #26 (**permalink**) 05-02-2009, 10:16 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)