?
Solved

unknown SQL authentication accounts and risks

Posted on 2014-08-05
5
Medium Priority
?
209 Views
Last Modified: 2014-08-21
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?
0
Comment
Question by:pma111
5 Comments
 
LVL 84

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 400 total points
ID: 40242805
Simply don't use SA authentication and enforce Windows Authentication set up some service accounts for them to use.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 400 total points
ID: 40242807
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
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 400 total points
ID: 40243067
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
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 400 total points
ID: 40243694
> 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
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 400 total points
ID: 40247841
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question