unknown SQL authentication accounts and risks

We are having a bit of an issue with our DBA department when doing enterprise level access management checks. When it comes to SQL databases, for SQL authentication, we can easily produce a list of which SQL, local windows or domain users can access the instance – but specific to SQL authentication – when we provide a list to the DBA to ask what are these logins used for, whey have they the permissions they do, and which staff use them – more often than not then don’t seem to have a clue. They say often the accounts are tied to the application interacting with the database server – but I don’t think that’s sufficient:

1.      What if any risks can you think of with the DBA not knowing what the accounts are used for/who access them?
2.      And is there any general best practice on what documentation/notes to store about each account for future audits/reviews? i.e. what do you store about your accounts so you know straight away if asked what they are used for?
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
Simply don't use SA authentication and enforce Windows Authentication set up some service accounts for them to use.
0
Jim P.Commented:
That is a large question. But it is manageable.

You need to have your auditors come down on your DBA's and have them search down each SQL userid and identify its usage.

Then another one that needs an audit is who is in the admin user groups because they generally  have full access to SQL Servers.

There was a recent editorial about similar issues on SQL Server Central. It might be a good read: Elevation of Privileges
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
The starting Point is to capture logon in a database along with server/client name/IP
From therer you can figure out where the accounts comes from.

Windows auth has been around since sql2000. It's time to put pressure on the vendors to support windows authentiscation from their applications.

Regards Marten
0
Vadim RappCommented:
> What if any risks can you think of with the DBA not knowing what the accounts are used for/who access them?

If dba creates sql server auth login for a given application, and then that login is used only in that application, with hardcoded password, it's generally OK, because nobody but the developer knows about even the existence of this account, and not knowing what to crack is the best protection. As long as each such login is just for one application and is not shared with other developers and other applications, it's OK. There is possibility however that the given developer will start using this login for his own purposes, but this can be alleviated by giving the login the very few permissions that are actually required for the given application, i.e. only on those few database objects it works with, and only with read/write permissions  it needs.

There may be good idea to even name those application-targeted logins in some way so that the login name would reflect the application it is for.


>  i.e. what do you store about your accounts so you know straight away if asked what they are used for?

"account X, password xxx, created by xxx by request of xxx on date xxx, for application xxx"
0
Eugene ZCommented:
check

Reviewing SQL Server Permissions
http://www.techrepublic.com/blog/the-enterprise-cloud/reviewing-sql-server-permissions/

Identify with high level permissions 'sa'

run sql profiler to see if anything (apps)/anyone  using sql login and make call to delete this login and\or just document

and much\much more


and read SQL_Server_2012_Security_Best_Practice_Whitepaper_
http://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx) from operational perspective (compliance, encryption, access control, authentication, network security, and auditing)



1.      What if any risks can you think of with the DBA not knowing what the accounts are used for/who access them?
 --- it depends:  if you have some new DBA it is normal do not know such things
    if DBA is not new and does not know how to track sql login activities\permissions you may have issues

 2.      And is there any general best practice on what documentation/notes to store about each account for future audits/reviews? i.e. what do you store about your accounts so you know straight away if asked what they are used for?

you may use SOX checklist variation

Sarbanes-Oxley compliance checklist: IT security and SQL audits
http://searchsqlserver.techtarget.com/tip/Sarbanes-Oxley-compliance-checklist-IT-security-and-SQL-audits
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.