How to access the DB with Integrated Security

Hello,

I'm new to ASP, but developping in Sql for years.

What we would like to have is that the user is accessing the database over it's own Windows Logon. Our triggers log quite some changes and are using UserName() for this. I've treid to force the IIS to accept Windows Integration only, the SqlDataSource users a connection that has Integrated Security = True. But when connection to the site i'm gatting error that there is no trusted connection for the user . (dot) ...

I suppose i'm missing something but could you give me a hint where to start looking.... THX

Hello my friend,

Is the database and the website running on the same domain? I ask because it may be that the SQL Server has no recognition of the Windows account being used on the website server. To cater for future requirements, including the ability of third parties to be able to access this data source, I would use SQL Server authentication (but not the sa account!). Create a sql server user with restrictions on the tables/views/procedures/etc they can use. This is much more secure and extendable than the Windows approach.

Kind regards

Scotty

|||

It is on the same domain.

The mean reason for trying this approach is the triggering, in our classic applications we use the windowslogon and so we can use the username() function in our triggers to log who has changed what values when ... when using a common user we would loose this functionallity, That's why we tried to do this.

A bit stange to me is the proposal of this sql user. And i've seen it in most of the posts i've been reading on these problems, because, the first thing in every book on Sql Security is to close down the SqlServer Logins, and here you have to use them !!

|||

Yes and no.

You force the users to connect as SQL logins and then you restrict these logins as much as possible, as opposed to a Windows account. You can configure a sql user to only allow read-only access to 1 column in 1 table out of the whole database if necessary. Disallow everything and then allow as necessary is an approach all of the security books will endorse. I find that the best books on SQL come from Wrox.

Kind regards

Scotty