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 “environm
ent
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 App
s
are written to require direct Read/ Write access to our tables, so being abl
e
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) an
d
> the consensus seemed to be “no”. Thanks to those that replied, but now
I have
> another question. I currently have 2 environments. Our “legacy “enviro
nment
> is opened up wide security wise. Way to loose. Lots of folks have excessiv
e
> permissions in our SQL Servers. Our new environment is very tightly
> controlled. We only use SQL authentication for DB connections, and very fe
w
> 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 direc
t
> 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 usi
ng
> 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 A
pps
> are written to require direct Read/ Write access to our tables, so being a
ble
> to enforce data manipulation only through the use of Stored Procs will nev
er
> 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 t
o
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 no
t
be very secure even if the views were encrypted.
John
"Norman Yuan" wrote:

> You are confusing the authentication and authorization. In SQL Server, usi
ng
> Windows security means SQL Server authenticate user access by their window
s
> 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 authenticate
d
> 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 SQ
L
> 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 ever
y
> 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...
>
>|||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[vbcol=seagreen]
> be very secure even if the views were encrypted.
>
> John
> "Norman Yuan" wrote:
>
using[vbcol=seagreen]
windows[vbcol=seagreen]
on[vbcol=seagreen]
authenticated[vbcol=seagreen]
is[vbcol=seagreen]
SQL[vbcol=seagreen]
on[vbcol=seagreen]
every[vbcol=seagreen]
to[vbcol=seagreen]
one)[vbcol=seagreen]
I[vbcol=seagreen]
very[vbcol=seagreen]
logins.[vbcol=seagreen]
connection to[vbcol=seagreen]
our[vbcol=seagreen]
being[vbcol=seagreen]
Application[vbcol=seagreen]
what[vbcol=seagreen]|||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 certa
in
> 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...
> that
> to
> not
> using
> windows
> on
> authenticated
> is
> SQL
> on
> every
> to
> one)
> I
> very
> logins.
> connection to
> our
> being
> Application
> what
>
>|||> You can still use Windows
> Authentication along with the application role(s), but you will not get th
e
> 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...[vbcol=seagreen]
> 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 Authenticati
on
> 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 th
e
> auditability.
> John
> "ChrisR" wrote:
>|||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:

> Not is user names are audited, but if logins names are audited, then nothi
ng 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...
>