Changing security authentication type.

Hi,
in an existing instance of SQL, will changing the security authentication
from SQL & Windows logins, to only windows logins cause any issues?
Also, if a database has been created with hardly any security already, are
there any issues with introducing security further down the line. For
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?
TIAInline...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <stu@.blah.com> wrote in message
news:%23E25Hpb1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> 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
>|||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@.1568.com> wrote in message
news:%23tasa7b1GHA.480@.TK2MSFTNGP06.phx.gbl...
> Inline...
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Stu" <stu@.blah.com> wrote in message
> news:%23E25Hpb1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> It will block (lock out) any attempts to access the database using the sa
> account (or any other SQL Login).
>
>
> 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.
>
> 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.
>
>|||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@.blah.com> wrote in message
news:uSCBYwc1GHA.4648@.TK2MSFTNGP04.phx.gbl...
> 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@.1568.com> wrote in message
> news:%23tasa7b1GHA.480@.TK2MSFTNGP06.phx.gbl...
>|||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@.1568.com> wrote in message
news:%23LVeCAd1GHA.4116@.TK2MSFTNGP02.phx.gbl...
> 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@.blah.com> wrote in message
> news:uSCBYwc1GHA.4648@.TK2MSFTNGP04.phx.gbl...
>|||Inline...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <stu@.blah.com> wrote in message
news:uZXUeon1GHA.3908@.TK2MSFTNGP05.phx.gbl...
> 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 b
e in
the sysadmin role. (It has the same 'power' as [sa], but the security yo
u
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/Boo...isbn=190434707X

>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23LVeCAd1GHA.4116@.TK2MSFTNGP02.phx.gbl...
>