Re: Windows Authentication question

Mark Clark (M-Clark-nospam@xxxxxxxxxxxxx) writes:
Currently we use SQL authentication on SQL Server 2005, with a few
logins for our applications, because we are using NetWare for users. We
are planning on moving to Active Directory, and therefore I am
considering Windows Authentication. My concern is that if/when we
switch, users would then be able to use query analyzer or Management
Studio or other tools to log in directly to the server, whereas they
can't now. Is there some way to use Windows Auth. and allow them to
connect via an application but not "directly"?

No. Mgmt Studio is just another application.

Short of that, what other suggestions do you have? I am considering
removing the SQL authentication accounts so we don't have to maintain
them (change passwords every so often) and because from what I've read
Windows authentication is more secure. I am not opposed to staying with
SQL authentication if it makes more sense in this scenario.

The tested-and-tried solution for security in my opinion is to use
stored procedure. Then the users only need EXEC permissions on the stored
procedures. But if you are using ad-hoc statements today, switching
to stored procedures means a major rewrite.

An alternative is to have the application authenticate the users, and
then connect to SQL Server on your behalf. This appears to be what
you are doing today. But if the applications are three-tier apps, the
middle tier could run with its own login, and log into SQL Server with
that login over Windows authentication.

If your application is a two-tier app, the current construction is
certainly very insecure, as it's not difficult for a crafty user
to find the SQL logins and the passwords.

I have been starting to read about application roles, which look like a
possibility, except that some apps traverse more than one DB. From what
I've seen, when you need to do that, you have to start enabling and
granting things for guest, which I really don't want to do. It seems
like that's opening things up for possible trouble.

And the same issue applies to application roles on two-tier apps.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: what happend if deny login to "sa"??
    ... "Sue Hoegemeier" escribió en el mensaje ... And SQL Server does not allow you to use sp_droplogin to drop sa. ... If you want to prevent logins with sa, use Windows authentication only. ...
    (microsoft.public.sqlserver.connect)
  • Re: Need Help w/ SQLServer Express Authentication
    ... Windows Authentication can not be disabled using Mixed Authentication. ... You can only disable SQL Server authentication and Microsoft recommends using Windows Authentication when possible. ... Logins make your users to connect and perform their tasks in SQL Server. ...
    (microsoft.public.sqlserver.setup)
  • RE: Facing Error: 15401 while creating local logins
    ... I got that error today while doing some security accounts; ... I have SQL server installed on a cluster enviroment, ... 2005 with windows authentication, I am getting an Error: ... I am able to carete domain logins in sql server. ...
    (microsoft.public.sqlserver.security)
  • Re: Windows Authentication with IIS on separate machines
    ... Yes, setting Basic Authentication in IIS works, but the ... users must give their logins and passwords again. ... >> We're trying to make our Intranet pages use Sql Server ... >> Windows Authentication. ...
    (microsoft.public.sqlserver.security)
  • Re: Credentials not being passed with remote access
    ... allowed windows authentication to work, ... I had assumed johnx was a domain user rather than a local Windows account. ... johnx was set up as a local login on sql server. ...
    (microsoft.public.sqlserver.security)