Solved

SQL Server 2005 Security Logins Audit

Posted on 2009-05-08
27
445 Views
Last Modified: 2012-05-06
Hi

Environment:
Windows 2003 Forest (x86)
Windows 2003/2008 server (x86 / x64) STD and ENT editions
SQL 2005 (x86 / x64) STD and ENT editions

Maybe straight forward but I am in need of getting a report together pretty soon..!

What I am looking to do is use an input file of server hostnames (SQL servers) and run an audit on all the sql security logins on each server (including local groups and domain groups) and have the membership of these group made into an audit report?

I could take an input file from .csv and then a dsquery but I was hoping to see if there are better methods to run within sql to achieve this in a more automated manner?

I appreciate any guidance/pointers on where I may start (and yes, I have already began research on EE and Google ;-)

Cheers
Bry
0
Comment
Question by:BryanOakley
  • 14
  • 5
  • 5
  • +1
27 Comments
 
LVL 22

Expert Comment

by:Paka
ID: 24334350
Are you after the local machine logins and groups or are you after the sql accounts themselves?   If you're after the sql accounts try:

select * from master.sys.server_principals
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24334368
if you want to find sql account with principal, have a look at

http://www.sqlhub.com/2009/05/find-database-principal-and-its-member.html

SELECT

MemPri.Name as MemberPrincipal,     

RolePri.Name as RolePrincipal,

MemPri.Create_Date as DateCreated,

MemPri.Modify_date as DateModified,

MemPri.Type_Desc as Description

FROM

sys.database_role_members RoleMem

JOIN sys.database_principals RolePri ON RoleMem.role_principal_id = RolePri.principal_id

JOIN sys.database_principals MemPri ON RoleMem.member_principal_id = MemPri.principal_id

order by MemberPrincipal

Open in new window

0
 

Author Comment

by:BryanOakley
ID: 24334776
Paka & RiteshShah: Thanks for your responses, very much appreciated.

What I am looking to do, is to be able to query a bunch of my SQL servers, say from a text file and find all security logins on all sql servers (local and domain) and then enumerate the members of these groups and then output all this data to a report format, HTML or .CSV or similar or even a flat text file would do for now?

any pointers appreciated :-)

Cheers
Bry

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24334798
still not clear, all you want is, all login available in SQL server and output should be in CSV or HTML file.... is it something you want?
0
 

Author Comment

by:BryanOakley
ID: 24334880
Hi RiteshShah

Apologies, i'll try and be more clear of my requirements.

For a security compliance audit, I want to audit several SQL servers for all sql logins, I.e BUILTIN\administrators, DOMAIN\GROUPA, DOMAINGROUPB etc and to report on who the members of each group are (these can be both local groups and domain groups in active directory).

This will help identify all groups membership within the sql login realm.

I hope this makes more sense.Cheers
Bry
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24334955
well in that case my last script can give you "MemberPrincipal" which is login name and last column is "Decription" it shows you whether it is a SQL User or WIndows user. You can get that list in CSV file, have a look at how you can export sql results to CSV or TEXT file. have a look at my article.

http://www.sqlhub.com/2009/05/create-comma-separated-file-from-sql.html



0
 
LVL 22

Expert Comment

by:Paka
ID: 24335231
I suspect that this task will be a bit more difficult that just a SQL query.  Since Active Directory Groups can be nested, this task will probably take a sql query and combined with some VBScript to expand the groups that come out of the sql query.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24335270
You only suspect but I am sure this can't be done with just a simple SQL Query, it needs to have support of scriptting language, all one can do is, get list of all user (may be with the query I provided) , sort out windows user and write script in C# or in VB.NET with the help of system.directoryservices namespace and found its group and node, have a look at small script for ADSI

http://www.willasrari.com/blog/query-active-directory-users-using-c/000133.aspx

In SQL server, one can add ADSI as linked server but it will be more difficult than writing the simple script in C#

0
 
LVL 22

Expert Comment

by:Paka
ID: 24335575
Here's a VBScript that will iterate AD nested group membership:
http://www.tek-tips.com/viewthread.cfm?qid=1348699

If you don't have too many SQL servers, I'd recommend running the accounts query on each server manually, export them to separage files, clean the files up with Excel and then modify the script in the link above to expand the group membership.  If you could run the first three steps and give me a section of the output, I can help with the VBScript part of things.

0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24342831
I do not know if you were wanting a quick fix or how to set up a good audit routine to use going forward, but I think this will take several tools and steps and the best place to put it all together is probably with Powershell.  I am just getting into this myself so I can not provide the direct answer but here are some links to articles about Powershell starting with the first that is the list of articles by Muthusamy Anantha Kumar aka The MAK on Database Journal who has a plethora of information on Powershell, SMO and other SQL info and is the closest thing to an expert I have found so far.
http://www.databasejournal.com/article.php/3300441
http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/
HTH
CGL
0
 

Author Comment

by:BryanOakley
ID: 24342934
Hi CGLuttrell:

Thanks for your response - I am just checking the links you sent over.
I too am just beginning on the powershell route, so will be interesting reading.

In the short term, I am looking probably for a 'quick fix', audit report but I will be working on developing a more automated and robust auditing model.

Basically, I want to use something like the following:
exec xp_logininfo
and dump all the data I get returned (including local windows groups and AD domain groups that have associated sql login) to a report file - Ideally would be to split out the command to separate the local and domain groups into seperate files if possible?

I have about 40 sql servers that I need to audit so I would like to use an input list of server names if possible.


@ Paka: - Thanks for your offer of vbscript help, very appreciated. I am a wintel/sys admin by trade so I am comfortable with the vbscript/AD side of things, I just wanted to explore the possibiity of running these queries within SQL.

On that note, if I run the following:
EXECUTE MASTER..XP_CMDSHELL 'DSQUERY.exe group'
I get a return of groups etc - Do you happen to now how I may put filters in the syntax within sql (I.e, DSQUERY group -name "example" | DSGET group -members)???

Again all, thanks so much for your responses..!

Cheers
Bry

0
 

Author Comment

by:BryanOakley
ID: 24343078
Update:
I am now using the following powershell script (original script courtesy of  http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/ ) for which I am modifying to suit my needs ( (I have taken out the defined group to query back ALL groups on the sql server) and am working on running an AD search and enumerating all the nested domain groups memberships and also local windows groups members...

# Find a login or AD group on multiple servers
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
foreach ($svr in get-content "d:\scripts\AllServers.txt")
  {
            $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
 $srv.Logins | Select Parent, Name
  }


I shall update further when I get the query part working...
If anyone has any input for the query part, I would be most obliged..!

Cheers
Bry
0
 

Author Comment

by:BryanOakley
ID: 24343093
@ CGLuttrell: - Thanks for the Simple-Talk.com URL link ;-)
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24343140
Bryan, your welcome, glad to help.
whats the query part you are working on?  the xp_logininfo or the XP_CMDSHELL 'DSQUERY.exe group'?
I don't seem to have a system set up to try the xp_cmdshell but I can try to help with the xp_logininfo if you describe what you want from it.
cgl
0
 

Author Comment

by:BryanOakley
ID: 24343289
Hi CGL

Thanks buddy.
I am now looking at working with powershell scripts for this, as I think it will enable me to develop a more defined process for mass auditing of my SQL estate (I am not a DBA by trade).

I have found SQLPSX (http://www.codeplex.com/SQLPSX) to extend powershell with SQL specifics, so I am investing time in looking at these commands to see if it gives me what I need.

As for xp_logininfo:
Can I pipe xp_logininfo into a DSQUERY statement within SQL do you happen to know?

Cheers
Bry

0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24343594
Bryan,
I am not an admin so I am trying to understand what all the output I am seeing means and how to refine it, but this code will successfully take the output from xp_loginfo and pipe it to DSQUERY group -name <> | DSGET group -members and gather the results.  You might be able to make some since out of it and why some of the results get errors.  I was trying to grab the domain part and see if that helped clear some up but I made it worse because I did something wrong.  I don't know where you are but I stayed up all night playing with this so I've gotta get some rest now.
Later,
CGL
DECLARE @t TABLE (accountname NVARCHAR(100), type VARCHAR(50), privlege VARCHAR(50), mappedloginname NVARCHAR(100), permissionpath NVARCHAR(100), processed int DEFAULT 0, processed2 int DEFAULT 0)
 

INSERT INTO @t (accountname,[type],privlege,mappedloginname,permissionpath)  

EXEC xp_logininfo
 

DECLARE @an NVARCHAR(100)
 

WHILE EXISTS (SELECT accountname FROM @t GROUP BY accountname HAVING MAX(processed)=0)

BEGIN TRY

	 SELECT TOP 1 @an=accountname FROM @t GROUP BY accountname HAVING MAX(processed)=0

	 INSERT INTO @t (accountname,[type],privlege,mappedloginname,permissionpath)  

	 exec xp_logininfo @acctname = @an, @option='members'
 

	 UPDATE @t SET processed = 1 WHERE processed = 0 AND accountname = @an

END TRY

BEGIN CATCH

	UPDATE @t SET processed = 2 WHERE processed = 0 AND accountname = @an

END CATCH
 

SELECT DISTINCT * FROM @t
 

DECLARE @groupmembers TABLE (members NVARCHAR(100),accountname NVARCHAR(100), type VARCHAR(50), privlege VARCHAR(50), mappedloginname NVARCHAR(100), permissionpath NVARCHAR(100))

DECLARE @n NVARCHAR(100), @n0 NVARCHAR(100), @cmd NVARCHAR(1000)
 

WHILE EXISTS (SELECT accountname FROM @t GROUP BY accountname HAVING MAX(processed2)=0)

BEGIN TRY

	 SELECT TOP 1 @an=accountname FROM @t GROUP BY accountname HAVING MAX(processed2)=0

	 SET @n0 = SUBSTRING(@an,1,CHARINDEX('\',@an,1)-1)

	 SET @n = SUBSTRING(@an,CHARINDEX('\',@an,1)+1,LEN(@an))

	 set @cmd = 'DSQUERY.exe group -name "'+@n+'" | DSGET group -members'

	 PRINT 'now processing '+@an+' with command '+@cmd

	 INSERT INTO @groupmembers (members)  

	 EXECUTE MASTER..XP_CMDSHELL @cmd

	 

	 UPDATE @t SET processed2 = 1 WHERE processed2 = 0 AND accountname = @an

	 UPDATE @groupmembers SET accountname = @an WHERE accountname IS NULL -- assuming these last inserts are the null ones

END TRY

BEGIN CATCH

	UPDATE @t SET processed2 = 2 WHERE processed2 = 0 AND accountname = @an

END CATCH
 

SELECT * FROM @groupmembers

Open in new window

0
 

Author Comment

by:BryanOakley
ID: 24343675
Hi CGL

Wow, this is really heading towards what I am after and I really and I mean really, appreciate your time and effort on this, hats off to you kind sir..!

I will have a look at the dsquery stuff.
One thing, would it be straight forward to output all this info into an excell sheet?

Once again, thanks so much
Cheers
Bry
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24343680
good work CGLuttrell
0
 

Author Comment

by:BryanOakley
ID: 24343682
oh yes, also it would be great if I could read in a bunch of server names from a text file? Would this be fairly easy to do in the SQL query as it is in vbscript, poweshell etc?

Cheers
Bry
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24343711
I do not know of a way to put the output straight into excel, you would have to cut and paste from Management Studio.  If you are doing things aready in powershell and executing commands on the Sql boxes from there, what can you do with the output, can it be dumped to csv to use in excel?  
As for reading from a text file from inside the db, although that is definatelly possible, see http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/ , I would think managing it from the shell would be easier/better.
anyway, we will just keep building on this thing till we make it sing.
0
 

Author Comment

by:BryanOakley
ID: 24343785
Hi CGL

Thanks again for your input and I really appreciate the "we will just keep building on this thing till we make it sing.", that is extrememly generous of you..!

Within powershell we can export to excel in a variety of ways, including output-csv, out-file etc...
Maybe I should look at calling the .sql query you are developing form within a powershell script and see if we can get the output directed to excel that way? I'll play around and see what I can get done.

Also on that, within powershell I can use a text file as input with the <get-content> cmdlet - I just need to see if I can pipe this info into the sql query you wrote???

I know there are lots of questions from me on this (as I say, i'm not a DBA by trade but wintel/ad/exchange engineer :-) but would you know how we may handle the windows local groups in the sql statement?

I.e when I run a command to get the sqllogins, it also list the local groups - I would also like to run a local query for members in these groups...

A big ask I know :-)
Anyhow, you need to get some rest..! So i'll catch up with you later...

thanks again buddy your input is most valuable...
Cheers
Bry
0
 

Author Comment

by:BryanOakley
ID: 24354827
I haven't forgotten about this, just a work production issue came up and I have been busy with that :-(
I shall get back to this later today/tomorrow and update when I get something further.

Cheers
Bry
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24361584
Bryan,
thanks for the update.  I am in sort of the same situation, the executive sponsor of our company's largest project is in town this week and I am having to devote most of my time durring and after work to these meetings so I am very busy myself.  Let me know when you have an update, I will monitor and get back as quick as I can.
cgl
0
 

Author Comment

by:BryanOakley
ID: 24361762
Thanks CGLuttrell:

I will for sure update as soon as I get chance to have a further look at this, prod issue has developed and is swallowing up my time - I will be working on this again during this week (when I get the odd chance to) but will for sure be able to work on it this week-end without doubt..!

Again thanks for your involvement, very valuable and very appreciated..!

Cheers
Bry
0
 

Author Comment

by:BryanOakley
ID: 24461513
Hi

apologies for delay, workload is just huge at the moment...
I will get back with some code later in the week, as a DBA I work with has been looking at this now and has come up with something - I am just helping him with some dsquery | dsget syntaxes and we should be good to go...

As soon as I can, I will upload the code/sql statements that are being used..!

and hopefully soon, I will get to look at the Powershell and SMO..

Cheers
Bry
0
 

Author Comment

by:BryanOakley
ID: 24561785
Hi

Again apologies for delay - In the middle of a massive migration project and it's just trying to find the time to get this solution worked on..!

I will endeavour to get some further code up here v.soon...

Thanks again
Bry
0
 

Author Closing Comment

by:BryanOakley
ID: 31579340
Hi CGLuttrell

Sorry for delay - The DBA chap is a bit reluctant to hand-up the code he has done (for the moment anyhow) - so I guess I will award the points to you for your stellar effort and time in the initial phases...

It is very appreciated.

@ CGLuttrell - if you want to PM me so that I can notify *if* I get the full code from the DBA, pls contact me at (rockdabeatz AT gmail DOT com)

Thanks again all for your responses and assistence - I am just sorry I cannot upload a full process :-(

Cheers
bry
0

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Disabling the Directory Sync Service Account in Office 365 will stop directory synchronization from working.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now