Confused about Windows Authentication

[JT]

Hi,

I read in the help file for MSSQL 2005 that Windows Authentication is
better than SQL Server authentication. Okay. So here's my scenario
and what I think I should do. Hopefully, you can offer me some
advice.

I have an application that interfaces with 2 MSSQL 2005 databases and
a DB2 database. For this example, I'll call them Master, Aux1 and
Aux2. Master contains most of the data and is MSSQL 2005. Aux1 is
MSSQL 2005 and contains a view that I need to read. Aux2 is DB2 and
contains 2 views. One is accessed directly. The other will be DTS'd
into a table in Master. I don't want the connection strings available
to the end-user, but I want to be able to change connection strings so
that the application is configurable to other databases. What I want
to do is

1) Create a user account in Master that gives it the rights to do
whatever is necessary. The application will restrict each user based
on Windows Authentication. I can secure this one connection string.

2) Create a table for storing the Aux1 and Aux2 connection strings
that can only be accessed by the account in step 1. Running the
application will give the appropriate users the rights to run the
forms for changing this table via the database user account.

This way the application can be ported to use different Aux1 and Aux2
databases but changing the connection strings cannot be done through
SQL Server Management Studio by just anyone with a valid Windows
login.

Is there a better way to approach this? If not, is there an
explanation on how to set up the user accounts that is better than
what I found in the help files? If I'm using Windows Authentication,
is it possible to have the application use a specific, unrestricted
account when the normal accounts for Windows logins are restricted to
just reading?

Thanks,

JT

[Uri Dimant]

JT

> Is there a better way to approach this? If not, is there an
> explanation on how to set up the user accounts that is better than
> what I found in the help files? If I'm using Windows Authentication,
> is it possible to have the application use a specific, unrestricted
> account when the normal accounts for Windows logins are restricted to
> just reading?

I think you on the right way. As I understood you cannot (at least I'm not
aware of) have two logins connected by one connection string , I mean to
activate the unrestricted account you will have to open a new connection
for him/her.
Have a look at application role to be activate by the application.
http://sqlcat.com/whitepapers/archive/2007/12/16/sql-server-2005-security-best-practices-operational-and-administrative-tasks.aspx

"JT" <j...@onemain.com> wrote in message
news:a937c621-3f48-47c9...@j22g2000hsf.googlegroups.com...

[JT]

Uri,

I took a look and there is some good information there. Now I'm
wondering if I should set up impersonation and either a special
Windows account or a SQL account and do an "Execute As" and "Revert"
or create a stored procedure that expects a special parameter to be
passed in that would only come from the application. This is not a
super sensitive application and I'm not worried that someone will
sniff out the password or parameter during application access, but I'd
still like it to be restricted access.

I'm still not sure which way to approach this, especially since it
appears that Windows Authentication or Mixed Mode is set at the server
level and not for each database. Can an application impersonate a
Windows account?

Thanks,

JT

On May 1, 1:43 am, "Uri Dimant" <u...@iscar.co.il> wrote:
> JT
>
> > Is there a better way to approach this?  If not, is there an
> > explanation on how to set up the user accounts that is  better than
> > what I found in the help files?  If I'm using Windows Authentication,
> > is it possible to have the application use a specific, unrestricted
> > account when the normal accounts for Windows logins are restricted to
> > just reading?
>
> I think you on the right way. As I understood  you cannot (at least I'm not
> aware of) have two logins connected  by one connection string , I mean to
> activate the unrestricted  account  you will have to open  a new connection
> for him/her.

> Have a look at application role  to be activate by the application.http://sqlcat.com/whitepapers/archive/2007/12/16/sql-server-2005-secu...

> > JT- Hide quoted text -
>
> - Show quoted text -