Re: Changing security authentication type.

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.

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?

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

once again, thanks for any further advice.


"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









.