Re: Changing security authentication type.

Inline...

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

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


"Stu" <stu@xxxxxxxx> wrote in message
news:uZXUeon1GHA.3908@xxxxxxxxxxxxxxxxxxxxxxx
Many thanks for your advice to date Arnie. Could i just run some more
questions past the group.

After reading your reply, i'm thinking that yes in time the sa account
will be withdrawn from use.
I'm just trying to get my head around a plan of attack on how it will take
place.

The first step i want to take is to create a group for suitable DBA's to
administer the server.
I'm guessing that i'm also going to have to create a DBA Admin user, which
will effectively take the place of the SA account, which will not as you
mention be a member of the sysadmin group / role.


A new DBA Admin User login would only be useful for SQL Login purposes.
You're moving away from SQL Logins.

It may be useful to create a domain group that contains those appointed to
be the SQL Administrators. You can then assign that domain group to the
[sysadmin] role in the server. Anyone that is a SQL Administrator must be in
the sysadmin role. (It has the same 'power' as [sa], but the security you
are moving toward is that a user must have their own domain account/password
in order to get into the server, and then if they are in the sysadmin role
they are allowed administrator priviledges. If you cannot create (or have
created) a domain group, then you could add each prospective administrator's
domain/username to the sysadmin role.

Also, if appropriate you can add users to the db_owner role in a database.
That role allows complete control of a database, create tables, etc., BUT
does not allow similar control for other databases on the same server. This
can be good for developers when a database is in development, and then
removed from developers when the database goes into production.


Is the switch over just as easy as selecting the windows authentication
radio button?
if so, if the changeover is made, if things go wrong, will the role back
be to retick mixed mode or would the initial changeover be not possible to
reverse?

Yes, you can check the button for Windows Authentication, and if things
break, click on SQL Authentication to return it back to mixed mode. -It will
take a few seconds as the server has to stop/restart.


also what changes will need to be done to the individual databases, just
changing the connection strings?


Each application will have to have it's connection strings changed to allow
for the new security model. BUT first, you will have created a database role
for the application users, and added the domain groups/accounts to that
role.

It will also be necessary to go through each database and provide
appropriate permissions to that role for Tables, Views, Stored Procedures,
Functions.

Different applications that have their own databases 'should' each have a
distinct database role, so that a user in permissions in one database cannot
inadvertently access data in another database.

once again, thanks for any further advice.

No problem.

For a good introduction into SQL Server security, see if you can find a copy
of Morris Lewis' book, SQL Server Security Distilled, ISBN 1-9043-47-07-X
http://www.abebooks.com/servlet/BookDetailsPL?bi=764994756&isbn=190434707X




"Arnie Rowland" <arnie@xxxxxxxx> wrote in message
news:%23LVeCAd1GHA.4116@xxxxxxxxxxxxxxxxxxxxxxx
Actually, I recommend that you take a deliberative approach to removing
all usage of the sa account, possibly with the eventual goal of switching
to Windows authentication.

Your first steps, however, are to determine the impact on the
applications of changing the connection strings. For some applications,
that will be relatively easy, for others, a re-deploy may be required and
that will take some time, effort and coordination.

Create one or more logins for the applications. Create one or more roles
for each database. Give those roles access to the appropriate databases.
give those roles permissions to the tables, views, stored procedures and
functions required by the applications. Place the logins in the new
roles. DO NOT put these logins in the sysadmin role.

Change the connections strings for the applications to use the new
logins.

You want to eventually remove any application usage of the sa account.

This is a goal, it may take some time to eventually put it into place.

But for now, take no action to change the sa account -until you
understand the implications, and have a plan to addresses any issues.

However, as long as the sa account password is readily known by
developers (and whomever else may know it), you really have no security
for your database. (And if the application is hacked, the hackers will
have admin privileges in the database.)

There may be other regulatory reasons that you need to tighten up the
security, i.e., HIPPA, SarBox, etc. The usage of the sa account will most
likely be flagged as a security audit failure.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

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


"Stu" <stu@xxxxxxxx> wrote in message
news:uSCBYwc1GHA.4648@xxxxxxxxxxxxxxxxxxxxxxx
ok, many thanks for the reply.

So in short you're saying to keep the "mixed" authentication, but to
secure the server down, the best path to take is to create an AD DBA
group, and configure key users to be in that group?

furthermore, to leave the sa account's permissions well alone!



"Arnie Rowland" <arnie@xxxxxxxx> wrote in message
news:%23tasa7b1GHA.480@xxxxxxxxxxxxxxxxxxxxxxx
Inline...

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

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


"Stu" <stu@xxxxxxxx> wrote in message
news:%23E25Hpb1GHA.1252@xxxxxxxxxxxxxxxxxxxxxxx
Hi,
in an existing instance of SQL, will changing the security
authentication from SQL & Windows logins, to only windows logins cause
any issues?


It will block (lock out) any attempts to access the database using the
sa account (or any other SQL Login).

Also, if a database has been created with hardly any security already,
are there any issues with introducing security further down the line.
For


There 'could' be substaintial disruption when you introduce security.
Consider a building where there were no keys necessary to enter the
building and the rooms, and suddenly keys were required to enter the
building, and also to enter rooms. Until everyone got all of the
correct keys for their needs, there would be major disruption. However,
if this was a well planned process, and appropraite keys were
distributed before all the locks were install, the disruption would be
minimal if at all.

example, a SQL server has been deployed already by my predecessor,
with authentication in SA & windows mode, and allowing pretty much
anyone access to SQL. If i was to create a DBA_Admin group and assign
admin writes only to that group, will it cause any problems in a
already functioning database?

Creating a domain/DBA_Admin group, providing that group login access to
the server, and also placing that group in the sysadmin server role,
will not cause any problems in a functioning database.

The problems will occur as you start locking down and removing
permissions from the sa account. And you 'should' do that. Applications
should not be using the sa account for database access since the sa
account can do 'anything' with and to the server.


TIA











.