Benefit of SQL authentication.

I asked the question last week about if there would ever be any benefit to
SQL authentication over WINNT (if I had the option of using either one) and
the consensus seemed to be â'noâ'. Thanks to those that replied, but now I have
another question. I currently have 2 environments. Our â'legacy â'environment
is opened up wide security wise. Way to loose. Lots of folks have excessive
permissions in our SQL Servers. Our new environment is very tightly
controlled. We only use SQL authentication for DB connections, and very few
people know those passwords. There is a proposal to start using WINNT Auth,
but here is the problem that I see. Currently, no end users can make direct
connections to the DB as they do not know the password to the SQL logins.
Essentially they have to use the app provided. But if I suddenly start using
WINNT Auth, each end user will then be able to make a direct connection to
the DB using Enterprise Manager, Access, whatever they want. Many of our Apps
are written to require direct Read/ Write access to our tables, so being able
to enforce data manipulation only through the use of Stored Procs will never
happen. The only way I know to be able to avoid this is to use Application
Roles, but I wanted to see if anyone else had any other ideas, and what
others were doing to remedy this?
TIA, ChrisRHi Chris
An application role will give you similar benefits to your current setup and
only minimum permissions need to be given to the windows accounts. You could
also only allow members of a specific windows group a login to restrict the
number of people that can connect.
John
"ChrisR" wrote:
> I asked the question last week about if there would ever be any benefit to
> SQL authentication over WINNT (if I had the option of using either one) and
> the consensus seemed to be â'noâ'. Thanks to those that replied, but now I have
> another question. I currently have 2 environments. Our â'legacy â'environment
> is opened up wide security wise. Way to loose. Lots of folks have excessive
> permissions in our SQL Servers. Our new environment is very tightly
> controlled. We only use SQL authentication for DB connections, and very few
> people know those passwords. There is a proposal to start using WINNT Auth,
> but here is the problem that I see. Currently, no end users can make direct
> connections to the DB as they do not know the password to the SQL logins.
> Essentially they have to use the app provided. But if I suddenly start using
> WINNT Auth, each end user will then be able to make a direct connection to
> the DB using Enterprise Manager, Access, whatever they want. Many of our Apps
> are written to require direct Read/ Write access to our tables, so being able
> to enforce data manipulation only through the use of Stored Procs will never
> happen. The only way I know to be able to avoid this is to use Application
> Roles, but I wanted to see if anyone else had any other ideas, and what
> others were doing to remedy this?
> TIA, ChrisR|||You are confusing the authentication and authorization. In SQL Server, using
Windows security means SQL Server authenticate user access by their windows
account (so no need to pass username/password since the user has logged on
to his computer with his windows user account and he has been authenticated
on who he is. However, just knowing who he is, does not mean you have to
give all accesses to him. He still can be denied any access at all, or is
given only necessary accesses.
Common and simple approach is create one or more windows user group. In SQL
Server, you assign different access permission to different groups. Say,
Group1 has all access permission, Group2 only hass "SELECT" permission on
Table1. Then you add userA and UseB to Group1, and allother users to
Group2... and so on. Very simple and easy to manage. You do not give every
windows user account to all access permission to the SQL Server.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
>I asked the question last week about if there would ever be any benefit to
> SQL authentication over WINNT (if I had the option of using either one)
> and
> the consensus seemed to be "no". Thanks to those that replied, but now I
> have
> another question. I currently have 2 environments. Our "legacy
> "environment
> is opened up wide security wise. Way to loose. Lots of folks have
> excessive
> permissions in our SQL Servers. Our new environment is very tightly
> controlled. We only use SQL authentication for DB connections, and very
> few
> people know those passwords. There is a proposal to start using WINNT
> Auth,
> but here is the problem that I see. Currently, no end users can make
> direct
> connections to the DB as they do not know the password to the SQL logins.
> Essentially they have to use the app provided. But if I suddenly start
> using
> WINNT Auth, each end user will then be able to make a direct connection to
> the DB using Enterprise Manager, Access, whatever they want. Many of our
> Apps
> are written to require direct Read/ Write access to our tables, so being
> able
> to enforce data manipulation only through the use of Stored Procs will
> never
> happen. The only way I know to be able to avoid this is to use Application
> Roles, but I wanted to see if anyone else had any other ideas, and what
> others were doing to remedy this?
> TIA, ChrisR|||Hi
I think that Chris' problem is that the design of his application means that
authenticated user require a wide range of authorization, including access to
the underlying tables. If authorization was solely restriced on Windows
Accounts or Groups then there would be no way to stop users bypassing his
applications and using other tools to manipulate the data. The application
role will make it harder for users to do this.
Another method would be to use views and have a security check (say a join
to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would not
be very secure even if the views were encrypted.
John
"Norman Yuan" wrote:
> You are confusing the authentication and authorization. In SQL Server, using
> Windows security means SQL Server authenticate user access by their windows
> account (so no need to pass username/password since the user has logged on
> to his computer with his windows user account and he has been authenticated
> on who he is. However, just knowing who he is, does not mean you have to
> give all accesses to him. He still can be denied any access at all, or is
> given only necessary accesses.
> Common and simple approach is create one or more windows user group. In SQL
> Server, you assign different access permission to different groups. Say,
> Group1 has all access permission, Group2 only hass "SELECT" permission on
> Table1. Then you add userA and UseB to Group1, and allother users to
> Group2... and so on. Very simple and easy to manage. You do not give every
> windows user account to all access permission to the SQL Server.
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
> >I asked the question last week about if there would ever be any benefit to
> > SQL authentication over WINNT (if I had the option of using either one)
> > and
> > the consensus seemed to be "no". Thanks to those that replied, but now I
> > have
> > another question. I currently have 2 environments. Our "legacy
> > "environment
> > is opened up wide security wise. Way to loose. Lots of folks have
> > excessive
> > permissions in our SQL Servers. Our new environment is very tightly
> > controlled. We only use SQL authentication for DB connections, and very
> > few
> > people know those passwords. There is a proposal to start using WINNT
> > Auth,
> > but here is the problem that I see. Currently, no end users can make
> > direct
> > connections to the DB as they do not know the password to the SQL logins.
> > Essentially they have to use the app provided. But if I suddenly start
> > using
> > WINNT Auth, each end user will then be able to make a direct connection to
> > the DB using Enterprise Manager, Access, whatever they want. Many of our
> > Apps
> > are written to require direct Read/ Write access to our tables, so being
> > able
> > to enforce data manipulation only through the use of Stored Procs will
> > never
> > happen. The only way I know to be able to avoid this is to use Application
> > Roles, but I wanted to see if anyone else had any other ideas, and what
> > others were doing to remedy this?
> >
> > TIA, ChrisR
>
>|||Yes, this is the dilema. As you referenced, I only want folks to use certain
apps, not whatever they want. I personally see this as a big problem with
using WINNT authentication.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D9AF704D-4D60-41C2-A789-1631DEB51B14@.microsoft.com...
> Hi
> I think that Chris' problem is that the design of his application means
that
> authenticated user require a wide range of authorization, including access
to
> the underlying tables. If authorization was solely restriced on Windows
> Accounts or Groups then there would be no way to stop users bypassing his
> applications and using other tools to manipulate the data. The application
> role will make it harder for users to do this.
> Another method would be to use views and have a security check (say a join
> to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would
not
> be very secure even if the views were encrypted.
>
> John
> "Norman Yuan" wrote:
> > You are confusing the authentication and authorization. In SQL Server,
using
> > Windows security means SQL Server authenticate user access by their
windows
> > account (so no need to pass username/password since the user has logged
on
> > to his computer with his windows user account and he has been
authenticated
> > on who he is. However, just knowing who he is, does not mean you have to
> > give all accesses to him. He still can be denied any access at all, or
is
> > given only necessary accesses.
> >
> > Common and simple approach is create one or more windows user group. In
SQL
> > Server, you assign different access permission to different groups. Say,
> > Group1 has all access permission, Group2 only hass "SELECT" permission
on
> > Table1. Then you add userA and UseB to Group1, and allother users to
> > Group2... and so on. Very simple and easy to manage. You do not give
every
> > windows user account to all access permission to the SQL Server.
> >
> >
> > "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> > news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
> > >I asked the question last week about if there would ever be any benefit
to
> > > SQL authentication over WINNT (if I had the option of using either
one)
> > > and
> > > the consensus seemed to be "no". Thanks to those that replied, but now
I
> > > have
> > > another question. I currently have 2 environments. Our "legacy
> > > "environment
> > > is opened up wide security wise. Way to loose. Lots of folks have
> > > excessive
> > > permissions in our SQL Servers. Our new environment is very tightly
> > > controlled. We only use SQL authentication for DB connections, and
very
> > > few
> > > people know those passwords. There is a proposal to start using WINNT
> > > Auth,
> > > but here is the problem that I see. Currently, no end users can make
> > > direct
> > > connections to the DB as they do not know the password to the SQL
logins.
> > > Essentially they have to use the app provided. But if I suddenly start
> > > using
> > > WINNT Auth, each end user will then be able to make a direct
connection to
> > > the DB using Enterprise Manager, Access, whatever they want. Many of
our
> > > Apps
> > > are written to require direct Read/ Write access to our tables, so
being
> > > able
> > > to enforce data manipulation only through the use of Stored Procs will
> > > never
> > > happen. The only way I know to be able to avoid this is to use
Application
> > > Roles, but I wanted to see if anyone else had any other ideas, and
what
> > > others were doing to remedy this?
> > >
> > > TIA, ChrisR
> >
> >
> >|||Hi Chris
I would argue that the way your system has been designed that causes your
problems and not Windows Authentication per se. Using Windows Authentication
provides better security and management. By using shared logins these
benefits are lost along with auditability. You can still use Windows
Authentication along with the application role(s), but you will not get the
auditability.
John
"ChrisR" wrote:
> Yes, this is the dilema. As you referenced, I only want folks to use certain
> apps, not whatever they want. I personally see this as a big problem with
> using WINNT authentication.
> Thanks.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D9AF704D-4D60-41C2-A789-1631DEB51B14@.microsoft.com...
> > Hi
> >
> > I think that Chris' problem is that the design of his application means
> that
> > authenticated user require a wide range of authorization, including access
> to
> > the underlying tables. If authorization was solely restriced on Windows
> > Accounts or Groups then there would be no way to stop users bypassing his
> > applications and using other tools to manipulate the data. The application
> > role will make it harder for users to do this.
> >
> > Another method would be to use views and have a security check (say a join
> > to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would
> not
> > be very secure even if the views were encrypted.
> >
> >
> > John
> >
> > "Norman Yuan" wrote:
> >
> > > You are confusing the authentication and authorization. In SQL Server,
> using
> > > Windows security means SQL Server authenticate user access by their
> windows
> > > account (so no need to pass username/password since the user has logged
> on
> > > to his computer with his windows user account and he has been
> authenticated
> > > on who he is. However, just knowing who he is, does not mean you have to
> > > give all accesses to him. He still can be denied any access at all, or
> is
> > > given only necessary accesses.
> > >
> > > Common and simple approach is create one or more windows user group. In
> SQL
> > > Server, you assign different access permission to different groups. Say,
> > > Group1 has all access permission, Group2 only hass "SELECT" permission
> on
> > > Table1. Then you add userA and UseB to Group1, and allother users to
> > > Group2... and so on. Very simple and easy to manage. You do not give
> every
> > > windows user account to all access permission to the SQL Server.
> > >
> > >
> > > "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> > > news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
> > > >I asked the question last week about if there would ever be any benefit
> to
> > > > SQL authentication over WINNT (if I had the option of using either
> one)
> > > > and
> > > > the consensus seemed to be "no". Thanks to those that replied, but now
> I
> > > > have
> > > > another question. I currently have 2 environments. Our "legacy
> > > > "environment
> > > > is opened up wide security wise. Way to loose. Lots of folks have
> > > > excessive
> > > > permissions in our SQL Servers. Our new environment is very tightly
> > > > controlled. We only use SQL authentication for DB connections, and
> very
> > > > few
> > > > people know those passwords. There is a proposal to start using WINNT
> > > > Auth,
> > > > but here is the problem that I see. Currently, no end users can make
> > > > direct
> > > > connections to the DB as they do not know the password to the SQL
> logins.
> > > > Essentially they have to use the app provided. But if I suddenly start
> > > > using
> > > > WINNT Auth, each end user will then be able to make a direct
> connection to
> > > > the DB using Enterprise Manager, Access, whatever they want. Many of
> our
> > > > Apps
> > > > are written to require direct Read/ Write access to our tables, so
> being
> > > > able
> > > > to enforce data manipulation only through the use of Stored Procs will
> > > > never
> > > > happen. The only way I know to be able to avoid this is to use
> Application
> > > > Roles, but I wanted to see if anyone else had any other ideas, and
> what
> > > > others were doing to remedy this?
> > > >
> > > > TIA, ChrisR
> > >
> > >
> > >
>
>|||> You can still use Windows
> Authentication along with the application role(s), but you will not get the
> auditability.
Not is user names are audited, but if logins names are audited, then nothing is lost. Or am I
missing something?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86706F06-7B2C-4CA7-A062-4CB6AD28B82A@.microsoft.com...
> Hi Chris
> I would argue that the way your system has been designed that causes your
> problems and not Windows Authentication per se. Using Windows Authentication
> provides better security and management. By using shared logins these
> benefits are lost along with auditability. You can still use Windows
> Authentication along with the application role(s), but you will not get the
> auditability.
> John
> "ChrisR" wrote:
>> Yes, this is the dilema. As you referenced, I only want folks to use certain
>> apps, not whatever they want. I personally see this as a big problem with
>> using WINNT authentication.
>> Thanks.
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:D9AF704D-4D60-41C2-A789-1631DEB51B14@.microsoft.com...
>> > Hi
>> >
>> > I think that Chris' problem is that the design of his application means
>> that
>> > authenticated user require a wide range of authorization, including access
>> to
>> > the underlying tables. If authorization was solely restriced on Windows
>> > Accounts or Groups then there would be no way to stop users bypassing his
>> > applications and using other tools to manipulate the data. The application
>> > role will make it harder for users to do this.
>> >
>> > Another method would be to use views and have a security check (say a join
>> > to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would
>> not
>> > be very secure even if the views were encrypted.
>> >
>> >
>> > John
>> >
>> > "Norman Yuan" wrote:
>> >
>> > > You are confusing the authentication and authorization. In SQL Server,
>> using
>> > > Windows security means SQL Server authenticate user access by their
>> windows
>> > > account (so no need to pass username/password since the user has logged
>> on
>> > > to his computer with his windows user account and he has been
>> authenticated
>> > > on who he is. However, just knowing who he is, does not mean you have to
>> > > give all accesses to him. He still can be denied any access at all, or
>> is
>> > > given only necessary accesses.
>> > >
>> > > Common and simple approach is create one or more windows user group. In
>> SQL
>> > > Server, you assign different access permission to different groups. Say,
>> > > Group1 has all access permission, Group2 only hass "SELECT" permission
>> on
>> > > Table1. Then you add userA and UseB to Group1, and allother users to
>> > > Group2... and so on. Very simple and easy to manage. You do not give
>> every
>> > > windows user account to all access permission to the SQL Server.
>> > >
>> > >
>> > > "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> > > news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
>> > > >I asked the question last week about if there would ever be any benefit
>> to
>> > > > SQL authentication over WINNT (if I had the option of using either
>> one)
>> > > > and
>> > > > the consensus seemed to be "no". Thanks to those that replied, but now
>> I
>> > > > have
>> > > > another question. I currently have 2 environments. Our "legacy
>> > > > "environment
>> > > > is opened up wide security wise. Way to loose. Lots of folks have
>> > > > excessive
>> > > > permissions in our SQL Servers. Our new environment is very tightly
>> > > > controlled. We only use SQL authentication for DB connections, and
>> very
>> > > > few
>> > > > people know those passwords. There is a proposal to start using WINNT
>> > > > Auth,
>> > > > but here is the problem that I see. Currently, no end users can make
>> > > > direct
>> > > > connections to the DB as they do not know the password to the SQL
>> logins.
>> > > > Essentially they have to use the app provided. But if I suddenly start
>> > > > using
>> > > > WINNT Auth, each end user will then be able to make a direct
>> connection to
>> > > > the DB using Enterprise Manager, Access, whatever they want. Many of
>> our
>> > > > Apps
>> > > > are written to require direct Read/ Write access to our tables, so
>> being
>> > > > able
>> > > > to enforce data manipulation only through the use of Stored Procs will
>> > > > never
>> > > > happen. The only way I know to be able to avoid this is to use
>> Application
>> > > > Roles, but I wanted to see if anyone else had any other ideas, and
>> what
>> > > > others were doing to remedy this?
>> > > >
>> > > > TIA, ChrisR
>> > >
>> > >
>> > >
>>|||Hi Tibor
No, you are correct! Using SYSTEM_USER would give them more information than
they could have currently with the shared SQL Server logins (unless they
share the windows logins as well!!)
John
"Tibor Karaszi" wrote:
> > You can still use Windows
> > Authentication along with the application role(s), but you will not get the
> > auditability.
> Not is user names are audited, but if logins names are audited, then nothing is lost. Or am I
> missing something?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86706F06-7B2C-4CA7-A062-4CB6AD28B82A@.microsoft.com...
> > Hi Chris
> >
> > I would argue that the way your system has been designed that causes your
> > problems and not Windows Authentication per se. Using Windows Authentication
> > provides better security and management. By using shared logins these
> > benefits are lost along with auditability. You can still use Windows
> > Authentication along with the application role(s), but you will not get the
> > auditability.
> >
> > John
> >
> > "ChrisR" wrote:
> >
> >> Yes, this is the dilema. As you referenced, I only want folks to use certain
> >> apps, not whatever they want. I personally see this as a big problem with
> >> using WINNT authentication.
> >>
> >> Thanks.
> >>
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:D9AF704D-4D60-41C2-A789-1631DEB51B14@.microsoft.com...
> >> > Hi
> >> >
> >> > I think that Chris' problem is that the design of his application means
> >> that
> >> > authenticated user require a wide range of authorization, including access
> >> to
> >> > the underlying tables. If authorization was solely restriced on Windows
> >> > Accounts or Groups then there would be no way to stop users bypassing his
> >> > applications and using other tools to manipulate the data. The application
> >> > role will make it harder for users to do this.
> >> >
> >> > Another method would be to use views and have a security check (say a join
> >> > to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would
> >> not
> >> > be very secure even if the views were encrypted.
> >> >
> >> >
> >> > John
> >> >
> >> > "Norman Yuan" wrote:
> >> >
> >> > > You are confusing the authentication and authorization. In SQL Server,
> >> using
> >> > > Windows security means SQL Server authenticate user access by their
> >> windows
> >> > > account (so no need to pass username/password since the user has logged
> >> on
> >> > > to his computer with his windows user account and he has been
> >> authenticated
> >> > > on who he is. However, just knowing who he is, does not mean you have to
> >> > > give all accesses to him. He still can be denied any access at all, or
> >> is
> >> > > given only necessary accesses.
> >> > >
> >> > > Common and simple approach is create one or more windows user group. In
> >> SQL
> >> > > Server, you assign different access permission to different groups. Say,
> >> > > Group1 has all access permission, Group2 only hass "SELECT" permission
> >> on
> >> > > Table1. Then you add userA and UseB to Group1, and allother users to
> >> > > Group2... and so on. Very simple and easy to manage. You do not give
> >> every
> >> > > windows user account to all access permission to the SQL Server.
> >> > >
> >> > >
> >> > > "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> >> > > news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
> >> > > >I asked the question last week about if there would ever be any benefit
> >> to
> >> > > > SQL authentication over WINNT (if I had the option of using either
> >> one)
> >> > > > and
> >> > > > the consensus seemed to be "no". Thanks to those that replied, but now
> >> I
> >> > > > have
> >> > > > another question. I currently have 2 environments. Our "legacy
> >> > > > "environment
> >> > > > is opened up wide security wise. Way to loose. Lots of folks have
> >> > > > excessive
> >> > > > permissions in our SQL Servers. Our new environment is very tightly
> >> > > > controlled. We only use SQL authentication for DB connections, and
> >> very
> >> > > > few
> >> > > > people know those passwords. There is a proposal to start using WINNT
> >> > > > Auth,
> >> > > > but here is the problem that I see. Currently, no end users can make
> >> > > > direct
> >> > > > connections to the DB as they do not know the password to the SQL
> >> logins.
> >> > > > Essentially they have to use the app provided. But if I suddenly start
> >> > > > using
> >> > > > WINNT Auth, each end user will then be able to make a direct
> >> connection to
> >> > > > the DB using Enterprise Manager, Access, whatever they want. Many of
> >> our
> >> > > > Apps
> >> > > > are written to require direct Read/ Write access to our tables, so
> >> being
> >> > > > able
> >> > > > to enforce data manipulation only through the use of Stored Procs will
> >> > > > never
> >> > > > happen. The only way I know to be able to avoid this is to use
> >> Application
> >> > > > Roles, but I wanted to see if anyone else had any other ideas, and
> >> what
> >> > > > others were doing to remedy this?
> >> > > >
> >> > > > TIA, ChrisR
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>