Is there an alternative to disabling windows authentication?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jwbutler - 26 Oct 2006 22:15 GMT

I’m a third party developer and my database gets installed on all types of
network setups. I can’t control the active directory settings for the users
so I want to protect my database by only allowing SQL Server Authentication.
I’m using MSDE 2000.

I have a security manager as part of my system that the users interact with
to assign rights and permissions to database objects. I translate this into
SQL Server authentication. The only logins are sa and several default logins
I created for different aspects of my system. I removed the BUILTIN\
Administrators Group. The only database roles are db_accessadmin,
db_backupoperator, db_datareader, db_ddladmin, db_denydatareader,
db_denydatawriter, db_owner, db_securityadmin, and public. The only database
user is dbo (sa).

With this setup I thought I could block Windows authentication but from what
I’ve read this can not be done. Is there another way to accomplish this?

Reply to this Message

Arnie Rowland - 28 Oct 2006 19:23 GMT

Don't provide any Windows login accounts permissions to log into the SQL
Server. Remove domain accounts from the Local Administrators.

And to a more salient point: Why on earth are you providing all users
database access as 'sa'?

Since all users are 'sa', the roles are useless because they are all system
admins in the SQL Server. That is the most egregious security breach
imaginable. Any user that knows (or learns) how to use Excel to connect to
SQL Server, or installs an eval version of SQL Server and client tools
(meaning Enterprise Manager and Query Analyzer) will have the ability to
muck up your data and/or schema.

I surely hope that this isn't a regulated market that has to comply with
HIPAA or SARBOX -the application will fail the security audit.

Signature

Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

> I'm a third party developer and my database gets installed on all types of
> network setups. I can't control the active directory settings for the
[quoted text clipped - 19 lines]
> what
> I've read this can not be done. Is there another way to accomplish this?

Reply to this Message

jwbutler - 31 Oct 2006 21:54 GMT

Arnie,
Thanks for the advise. I must not have explained it well enough. The other
sql logins provide access to different modules of the system not users.

John

>Don't provide any Windows login accounts permissions to log into the SQL
>Server. Remove domain accounts from the Local Administrators.
[quoted text clipped - 17 lines]
>> what
>> I've read this can not be done. Is there another way to accomplish this?

Reply to this Message

Arnie Rowland - 31 Oct 2006 22:13 GMT

John,

You can't 'block' Windows Authentication -but you don't have to accept any
Windows login accounts into your server.

Don't map any domain accounts to SQL Logins or and/or database roles, and
don't provide any specific permissions to the PUBLIC role -and domain users
'should' be kept out of your database.

Signature

> Arnie,
> Thanks for the advise. I must not have explained it well enough. The
[quoted text clipped - 27 lines]
>>> I've read this can not be done. Is there another way to accomplish
>>> this?

Reply to this Message