≡ Menu

Transforming Event Log Data

Several months ago I described a solution for Delegated SQL Server Administration with Powershell. In the solution, the SqlProxy module audits all security administration activity to a custom Windows Event log. In this blog post, I’ll described a process to transform and incrementally load the audit data into a SQL Server table for reporting purposes.

Writing to the Event Log

First a quick review of how  SqlProxy module writes messages to the event log. This is important because as we’ll see in a moment, how the message is constructed helps in extracting Event log data. In the SqlProxy module I use a standard template in each function for logging messages to the Event log:

$PSUserName = $PSSenderInfo.UserInfo.Identity.Name
$logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage

The message is constructed using several built-in variables written as key/value pairs.

  1. The $PSSenderInfo variable is available inside of remote session and returns information about the user who started the PSSession. Since I’m using runas credentials I’ll grab the name of the person who is connected.
  2. $psBoundParameters contains a hashtable of the parameters and their values for the current function.
  3. This code may look a little odd, $eventID.”$($myinvocation.mycommand.name)”. I created hashtable called $eventid in the SqlProxy module to translate a function name from a an EventId. Since $myinvocation.mycommand.name returns the function name I’ll use this as the hashtable key as shown below:
$EventID = @{
"Add-SqlDatabaseRoleMember"=0
"Add-SqlLogin"=1
"Add-SqlServerRoleMember"=2
"Add-SqlUser"=3
"Remove-SqlDatabaseRoleMember"=4
"Remove-SqlLogin"=5
"Remove-SqlServerRoleMember"=6
"Remove-SqlUser"=7
"Rename-SqlLogin"=8
"Set-SqlLogin"=9
"Set-SqlLoginDefaultDatabase"=10
}

The write-sqlproxlog function is just a wrapper around write-eventlog as follows:

 

function Write-SqlProxyLog
{
    param(
    [Parameter(Position=0, Mandatory=$true)] $EventID,
    [Parameter(Position=1, Mandatory=$true)] $Message,
    [Parameter(Position=2, Mandatory=$false)] $EntryType='SuccessAudit'
    )
 
    write-eventlog -logname SqlProxy -source SqlProxy -eventID $eventID -message $message -EntryType $EntryType
 
} #Write-SqlProxyLog

A typical event log entry will look like this:

SqlProxEvtLog

Extracting Data from the Eventlog

In order to load the Eventlog data into a SQL Server table I created a module called SqlTools which is collection of functions I use frequently for querying and loading data. I’ve posted the module here.

The initial load script as shown below makes use of SqlTools module:

import-module SqlTools
 
$ComputerName = 'Z001'
$ServerInstance = 'Z002sql1'
$Database = 'SqlProxy'
 
$dt = Get-EventLog -LogName SqlProxy -ComputerName $ComputerName -EntryType 'SuccessAudit' | % { $ht = ($_.Message -replace "\","/") |
    ConvertFrom-StringData; $xml = new-object psobject -Property $ht | ConvertTo-Xml -NoTypeInformation -As String;
    new-object psobject -Property @{'Index' = $_.Index; 'TimeGenerated'=$_.TimeGenerated;
    'EventId'=$_.EventId; 'MessageXml'=$xml} } | Out-DataTable
 
Add-SqlTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -DataTable $dt -AsScript | clip

At this point I’ll paste the T-SQL script into SSMS, modify column data types, null/not null, add primary key and finally create the table:

 

CREATE TABLE [dbo].[SqlProxyLog](
	[EventId] [int] NOT NULL,
	[TimeGenerated] [datetime] NOT NULL,
	[MessageXml] [xml] NOT NULL,
	[Index] [int] NOT NULL,
 CONSTRAINT [PK_SqlProxyLog] PRIMARY KEY CLUSTERED
(
	[TimeGenerated] ASC,
	[Index] ASC
)
)

Then I’ll return to Powershell to execute the write-datatable function:

Write-DataTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -Data $dt

In order to incrementally load only new events, I’ll modify the get-sqlproxylog.ps1 to first grab the max timegenerated or 1900-01-01 if its null and then use value for the -After param of the Get-Eventlog cmdlet:

import-module SqlTools
 
$ComputerName = 'Z001'
$ServerInstance = 'Z002sql1'
$Database = 'SqlProxy'
 
$query = "SELECT ISNULL(MAX(TimeGenerated),'1900-01-01') AS TimeGenerated FROM dbo.SqlProxyLog"
$maxDtm = invoke-sqlcmd2 -ServerInstance $ServerInstance -Database $Database -Query $query | select -ExpandProperty TimeGenerated
 
$dt = Get-EventLog -LogName SqlProxy -ComputerName $ComputerName -EntryType 'SuccessAudit' -After $maxDtm | % { $ht = ($_.Message -replace "\","/") |
    ConvertFrom-StringData; $xml = new-object psobject -Property $ht | ConvertTo-Xml -NoTypeInformation -As String;
    new-object psobject -Property @{'Index' = $_.Index; 'TimeGenerated'=$_.TimeGenerated;
    'EventId'=$_.EventId; 'MessageXml'=$xml} } | Out-DataTable
 
if ($dt) {
    Write-DataTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -Data $dt
}

Some interesting points about this script:

  • Since the message data is stored as key/value pairs, the built-in ConvertFrom-StringData cmdlet is used to create the hashtable $ht
  • The hashtable is then used to create a psobject
  • The psobject is converted into XML using ConverTo-Xml.

One minor issue I ran into is with unenclosed backslashes. If backslashes are enclosed in quotes its fine, if not it causes an error with ConvertFrom-StringData, so I replace them with forward slashes.

Data Is Loaded, Now What?

After I’ve loaded the data I’ll use XQuery to shred the message XML column into a relational data set. I created a function and view in SQL Server for this purpose:

CREATE FUNCTION [dbo].[ufn_GetEventMessage] (@MessageXml XML)
RETURNS @Message TABLE
(
	 ChangeOrder VARCHAR(128) NULL
	,dbname  VARCHAR(128) NULL
	,PSUserName   VARCHAR(255) NULL
	,name   VARCHAR(128) NULL
	,rolename   VARCHAR(128) NULL
	,[Description]   VARCHAR(128) NULL
	,sqlserver   VARCHAR(128) NULL
	,loginame   VARCHAR(128) NULL
	,[login]   VARCHAR(128) NULL
	,DefaultDatabase VARCHAR(128) NULL
)
AS
BEGIN
	INSERT @Message (ChangeOrder,dbname,PSUserName,name,rolename,Description,sqlserver,loginame,[login],DefaultDatabase)
	SELECT
	 Objects.Object.query('Property[@Name="ChangeOrder"]').value('.', 'varchar(128)') AS ChangeOrder
	,Objects.Object.query('Property[@Name="dbname"]').value('.', 'varchar(128)') AS dbname
	,Objects.Object.query('Property[@Name="PSUserName"]').value('.', 'varchar(128)') AS PSUserName
	,Objects.Object.query('Property[@Name="name"]').value('.', 'varchar(128)') AS name
	,Objects.Object.query('Property[@Name="rolename"]').value('.', 'varchar(128)') AS rolename
	,Objects.Object.query('Property[@Name="Description"]').value('.', 'varchar(128)') AS [Description]
	,Objects.Object.query('Property[@Name="sqlserver"]').value('.', 'varchar(128)') AS sqlserver
	,Objects.Object.query('Property[@Name="loginame"]').value('.', 'varchar(128)') AS loginame
	,Objects.Object.query('Property[@Name="login"]').value('.', 'varchar(128)') AS [login]
	,Objects.Object.query('Property[@Name="DefaultDatabase"]').value('.', 'varchar(128)') AS DefaultDatabase
	FROM @MessageXml.nodes('/Objects/Object') AS Objects(Object)
RETURN
END;
GO
 
CREATE VIEW [dbo].[vw_SqlProxyLog]
AS
SELECT l.*, m.*
FROM dbo.SqlProxyLog l
CROSS APPLY dbo.ufn_GetEventMessage(l.MessageXml) m
GO

The function requires I define all the possible attributes as columns. Here’s an example of parsing a typical message:

DECLARE @MessageXml XML
SET @MessageXml =
'<Objects><Object><Property Name="DefaultDatabase">master</Property>
<Property Name="PasswordPolicyEnforced">False</Property>
<Property Name="Description" />
<Property Name="sqlserver">[Z002/SQL1]</Property>
<Property Name="password">System.Security.SecureString</Property>
<Property Name="name">test3</Property>
<Property Name="PasswordExpirationEnabled">False</Property>
<Property Name="ChangeOrder">1234</Property>
<Property Name="logintype">SqlLogin</Property>
<Property Name="PSUserName">Z002/u00</Property>
</Object></Objects>'
 
SELECT * FROM dbo.ufn_GetEventMessage(@MessageXMl)
ChangeOrder PSUserName name sqlserver DefaultDatabase
1234 Z002/u00 test3 [Z002/SQL1] master

Finally I’ll add reference table for the SqlProxyEventType:

CREATE TABLE [dbo].[SqlProxyEventType](
	[EventName] [varchar](50) NOT NULL,
	[EventId] [smallint] NOT NULL,
 CONSTRAINT [PK_SqlProxyEventType] PRIMARY KEY CLUSTERED
(
	[EventId] ASC
)
);
 
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlDatabaseRoleMember', 0)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlLogin', 1)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlServerRoleMember', 2)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlUser', 3)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlDatabaseRoleMember', 4)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlLogin', 5)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlServerRoleMember', 6)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlUser', 7)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Rename-SqlLogin', 8)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Set-SqlLogin', 9)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Set-SqlLoginDefaultDatabase', 10)

The last thing I’ll do is setup a schedule SQL Agent job to run the get-sqlproxylog.ps1 script:

–Setup SQL Job with CmdExec Job Step:

–C:WindowsSystem32WindowsPowerShellv1.0powershell.exe -NoProfile -Command c:scriptsget-sqlproxylog.ps1

Summary

This post demonstrated extracting information from a specially crafted message in an event log and loading the data into a SQL Server table. Its kind of interesting to think about how easy it is to transform data from one thing to the next using Powershell. In this case the data was converted six times:

  1. To an Event log message data stored as key/value pairs using Write-Eventlog/Get-Eventlog
  2. To a hashtable using ConvertFrom-StringData
  3. To a psobject using New-Object -property hashtable
  4. To a DataTable using Out-DataTable
  5. Into a SQL Server table using Write-DataTable
  6. Out of a SQL Server table XML column to relational data set using XQuery

That said, next time I should probably just write the damn thing directly to a database to begin with.

{ 2 comments }

Reconciling SPNs

As part of troubleshooting Kerberos authentication for SQL Server I had to verify SPNs so I thought I’d blog about the process I went through. Sometimes I think a post blog is nothing more than public documentation of  a complex problem …

Get SQL Server SPNs from Active Directory

Before Powershell I would use the command-line utility setspn.exe to retrieve a list of SPNs for a given account. The syntax is:

setspn -L AccountName

Although this works reasonably well there are a couple of issues first the  output is text instead of objects which means I’d have to do a lot parsing and if you find you’re parsing text in Powershell too much there’s a high likelihood you’re doing it the hard way!. The second issue with setspn -L is that it expects an account and doesn’t retrieve ALL SPNs for a given service. For these reasons I created a script called Get-SqlSpn which will query Active Directory for all SQL Server SPNs. Querying AD for SPNs still requires a bit of parsing (I’ve taken care of this for you in the Get-SqlSpn function), but not near as much as starting from setspn. Let’s look at how I use Get-SqlSpn…

Using Get-SqlSpn

1. Download Get-SqlSpn
2. Source the function

. ./Get-Sqlspn.ps1

#Get the SQL Server SPNs

 $spns = Get-SqlSpn

If I’m interactively exploring data I like to use a tool for that specific purpose which for me is either loading the data into SQL Server or using Excel. For this particular I think Excel is a better fit. There is some tidying up and normalizing data that needs to be done which is really easy with Excel. Although you could mess with additional scripts to export data directly into Excel or or convert CSV files–a quick and dirty way to get data into Excel is simply copy and paste from out-gridview:
3. Open a new Excel document and rename a worksheet servers
4. Get Column Headers

$object = $spns | select -first 1
$ht = @{}
foreach($property in $object.PsObject.get_properties()) {
  $ht.add($property.Name.ToString(),$property.Name.ToString())
}

5. Copy/Paste heading row to Excel (Ctrl-A, Ctrl-C)

new-object psobject -Property $ht | out-gridview

6. Copy/Paste spns to Excel (Ctrl-A, Ctrl-C)

$spns | out-gridview

Now that you have a list of all SQL Server SPNs from AD, let’s grab some information from all of our SQL Servers to match against. For this purpose I created a script called Get-SqlWmi..

Using Get-SqlWmi

1. Download Get-SqlWmi and Invoke-Sqlcmd2
2. Source the functions

. ./invoke-sqlcmd2.ps1
. ./get-sqlwmi.ps1

I use a  query against System Center Configuration Manager database to pull in a list of SQL Servers. A second approach, if you maintain a SQL Server Center Management Server (CMS)  is to query sysmanagement tables. I’ll then pipe the list of servers to the get-sqlwmi function:

$servers = invoke-sqlcmd2 -ServerInstance 'smsserversql10' -Database 'dbautility' -query "select server_name AS 'ComputerName' from sms_sql_server_name_vw" | get-sqlwmi

3. Rename Excel worksheet Sheet2 to servers
4. Get Column Headers

$object = $servers | select -first 1
$ht = @{}
foreach($property in $object.PsObject.get_properties()) {
   $ht.add($property.Name.ToString(),$property.Name.ToString())
}

5. Copy/Paste heading row into Excel

new-object psobject -Property $ht | out-gridview

6. Copy/Paste servers to Excel

$servers | out-gridview

Now that I collected the data, I start analyzing…

Analyzing SPNs

Note: The steps which follow  involve using some basic data analysis functions in Excel. If you’re not sure how to filter, define named ranges or use Excel functions you may brush up on Excel. I’ve also included an Excel document with the appropriate fields and formulas for download here.

First I’ll look for invalid SPNs. I’ve seen issues where people will manually create SPNs incorrectly. The correct format is:

MSSQLSvc/<SQL Server computer name>:1433 AccountName

MSSQLSvc/<SQL Server FQDN>:1433  AccountName

Strangely enough I’ve seen semi-colons or commas instead of colons used which simply doesn’t work. The easiest way to find these SPNs is to use the Text Filter using contains ; or ,

The next thing I’ll do is normalize the data. Get-SqlSpn lists the accounts without the domain prefix, while Get-SqlWmi does so I’ll do a global replace to remove the domain slash.

In order to define a unique key, I’ll create a column which combines the SPN and service account name:

= B6 & ” ”  &C6

 

I’ll name the column searchSPN, this will allow me to do matching against the server list as we’ll see in a moment.

On the servers worksheet I’ll create two new columns of spn and combined as follows:

= “MSSQLSvc/” & D2 & “.contoso.com:” &A2

= E2 & ” ” &C2

I’ll then name the combine column (unique key) searchServer

Next I’ll add a column called matched to each worksheet and use the MATCH function to look for well, matches between SPN and Server lists:

=MATCH(E2,searchServer,0)

=MATCH(F2,searchSpn,0)

If there’s a match the match column will list the row number of corresponding match and if not you’ll see #NA. You can then filter on #NA and try to figure out why there are mismatches. Did I miss a server in inventory? Are SPNs defined for servers no longer on the network, etc.

And finally I’ll add one more column of a setspn command to delete or add SPNs using a Excel formula:

=”setspn -D ” & E2

=”setspn -A ” & F2

It may seem a little odd to build up commands in this way rather than using Powershell script, but I’m really paranoid about SPNs and want to verify each action. If you’re not careful you can cause some authentication problems by deleting needed SPNs or adding duplicates.

Note: a quick way to find duplicate SPNs is to use the command-line setspn -T * -X. You can also use  Excel’s conditional formatting to quickly identify duplicate values as described in here I’ve seen duplicate SPNs cause SQL Server to fallback to NTLM instead of using Kerberos. Searching for duplicate SPNs should be part of  your reconciliation. To fix simply remove the duplicate SPN.

Summary

This post demonstrated how to reconcile SQL Server SPNs between Active Directory and the SQL Server services. I think its  important to point out that you don’t need to take a 100% Powershell approach to a problem. In this example I could have used various Powershell cmdlets (compare-object, where-object, group-object, etc.), however for moderate to complex data analysis I prefer to use tools suited to task of data analysis either Excel or SQL Server. I’ll still use Powershell to gather the data and for simple data analysis tasks.

{ 1 comment }

Sharing Profiles

A question came up in a class I was teaching:  “How do you share your Powershell profiles across accounts?”

Well, there’s documented way of sharing profiles on the same machine where you create an All Users profile. As stated in help about_Profiles Powershell will look in the following locations for profiles:

        Name                               Description
        -----------                        -----------
        $Profile                           Current User,Current Host
        $Profile.CurrentUserCurrentHost    Current User,Current Host
        $Profile.CurrentUserAllHosts       Current User,All Hosts
        $Profile.AllUsersCurrentHost       All Users, Current Host
        $Profile.AllUsersAllHosts          All Users, All Hosts

The All Users profile creation is a little odd in that you would need to create your profiles in the $pshome directory instead of the $home directory. The $pshome directory is the location where powershell.exe is installed i.e. C:WindowsSystem32WindowsPowerShellv1.0 while $home is under <user>DocumentsWindowsPowershell.

I don’t like messing with storing my shared profiles in a system directory, so instead I use a different technique…

Enter Symlinks

I’ve previously blogged about Symlinks, but I didn’t mention I use them for sharing profiles. Note: This  requires Vista, Windows 7  or 2008 or higher OS. So here’s the steps to share profiles using symlinks:

  1. Create A WindowsPowershell folder or copy your existing WindowsPowershell to a shared location. I use C:UsersPublicDocuments.
  2. Make sure your accounts don’t have a WindowsPowershell folder under <user>Documents already.
  3. Start a classic Window command prompt as Administrator (that’s right don’t use Powershell to create symlinks)
  4. Change directories to the Documents folder for account you want to share profiles
  5. Run the following command
1
2
c:Usersu00Documents>mklink /D WindowsPowerShell C:usersPublicDocumentsWindowsPowerShell
symbolic link created for WindowsPowerShell <<===>> C:usersPublicDocumentsWindowsPowerShell

Repeat steps 4 and 5 for each account.

You now can now share profiles and modules across accounts on the same computer.

{ 0 comments }

Sarasota IT Pro Camp

Join system administrators IT professionals and database professionals in addition to managers at all levels who work with Microsoft technologies for the Sarasota IT Pro Camp on Saturday, February 18th, 2012.

IT Pro Camps are focused on serving the needs of  IT Pro’s (Windows system administrators). IT Pro’s haven’t had many events like our developer counterpart’s code camps and this is why I’m excited to be a part of IT Pro Camp. There’s a definite need for events which serve the IT Pro community. We’ve done Tampa, South Florida, Orlando and in two weeks we’ll add Sarasota to our growing list of cities. We’re also planning on Jacksonville, Pensacola and for the second year we’ll be returning to Tampa and South Florida. We hope to have dates for the remainder of 2012 finalized by the end of March. I’ll be sure to post an update once we work out scheduling and venues.

The Sarasota IT Pro Camp will feature 24 one-hour sessions on topics covering Powershell, BI, SQL Server, Cloud, Active Directory, System Center and Data Security. I’ll be presenting on Storing Powershell Output. Although there are many good sessions to choose from, I’d like to highlight a few sessions which peaked my interest:

We”ll have three sessions, on a topic we haven’t had at previous camps, Data Security.

Adam Malone – Cyber Crime and the FBI

Joseph Schorr – Rule 1: Cardio

Jeff Wolach – Introduction to Next Generation Firewalls

I think this this is one of the interesting things about having IT Pro Camps in different cities, there tends to be a strong technical community around particular disciplines and for Sarasota I’d say its Data Security.

Jose Chinchilla  (blog|twitter) is doing a two-part session on Business Intelligence.

Jose Chinchilla – Introduction to Microsoft Business Intelligence

Jose Chinchilla – Taking Business Intelligence to the next level with SharePoint 2010!

This is a good opportunity for attendees to get both an introduction and more advanced overview on business intelligence. Two-part series are kind of unique to community events and you tend not to see a two-part series at paid conferences.

Be sure to register to attend the free Sarasota IT Pro Camp event. A continental breakfast and lunch will be provided. Please tell your colleagues about IT Pro Camps. I look forward to seeing you there!

–Chad Miller

{ 0 comments }

Orlando IT Pro Camp 2012 Presentation

Presentation and scripts from my Storing Powershell Output session at Orlando IT Pro Camp 2012.

{ 0 comments }

Orlando IT Pro Camp

The Orlando IT Pro Camp marks our third event after Tampa and South Florida. As with the prior IT Pro Camps I’m helping put together the Orlando IT Pro Camp. Register to attend this free event on Saturday, January 21st. Orlando will be even bigger than Tampa or South Florida with 30 sessions on topics covering Hyper-v, Private Cloud, SharePoint, System Center Products, SQL Server, PowerShell, Active Directory, Windows 2008 R2, and Windows 7 . I’ll be presenting a PowerShell session on Storing Powershell Output.  A few sessions I’ve seen previously and highly recommend:

If you can’t make Orlando, the Sarasota IT Pro Camp will be held on Saturday, February 18th. 2012. See the IT Pro Camp site for more information.

{ 0 comments }

Importing CSV Files to SQL Server with PowerShell

Ed Wilson (Blog|Twitter) aka Scripting Guy is kicking off another guest blogger week  (Nov 28th 2011) with my guest blog post, Four Easy Ways to Import CSV Files to SQL Server with PowerShell. The post demonstrates the following approaches to importing CSVs into a SQL Server table:

  • T-SQL BULK INSERT command
  • LogParser command-line
  • LogParser COM-based scripting
  • A Windows Powershell-based approach using several functions
Most of the time I’ll I use BULK-INSERT or the Windows Powershell-based approach, although as explained in the post the ability of LogParser to automatically create a SQL table based on a CSV is pretty handy.
{ 1 comment }

Some SQL Server Security Housekeeping

Managing SQL Server security changes in mass is something which screams automate it. Let’s look a at few examples using either T-SQL, a Centeral Management Server and Powershell.

Task #1 Remove a SQL Server Login from the Sysadmin Role

Let’s say you’re given the task to remove a login from the sysadmin role on 50 servers.  For this task we can use the built-in system stored procedures sp_dropsrvrolemember  and sp_helpsrvrolemember.

Since we want to do this across multiple servers, we’ll use a multiserver query from the registered central management:

1. First create a before removal “backup” using sp_helpsrvrolemember:

sp_helpsrvrolemember 'sysadmin'

2. Save the output
3. Next run

EXEC sp_dropsrvrolemember 'CONTOSOSQL_SecurityAdmin', 'sysadmin'

Error handling is generally good thing, but there are cases were can do a task without much error handling especially if the task is interactive and you have backups. Although you can wrap some T-SQL code to check if the login exists on the server or if the login is member of the server before attempting to removing from the sysadmin, its not necessary any errors can safely be ignored. A Central Management Server is great at one off commands with good T-SQL coverage

Task #2 Remove Linked Server Login Mappings

After removing the login from the sysadmin you discover they are also mapped to sa on over 100  Linked Servers. Since you can’t  remove the login from the server because they still need non-administration access, your task to to remove the linked server login mapping. Changing linked server security  in mass is something where Powershell provides a good solution. You’ll probably want to backup the linked server by scripting out the linked server before any changes which is difficult to do in T-SQL, but easy with Powershell. Also removing a login mappings from all linked servers  is very procedural which is awkward in T-SQL. So here’s a Powershell solution I created in the form of a few Powershell filters and functions called LibraryLinkedServer.

. .LibraryLinkedServer.ps1
$logins = @(
'ContosoBill'
'ContosoJohn'
'ContosoJill'
)
Get-CMRegisteredServer "Z001SQL1" "PRD" | Backup-LinkedServer -LinkedServerLogins $logins
Get-CMRegisteredServer "Z00SQL1" "PRD" | Remove-LinkedServerLogin -LinkedServerLogins $logins

I would suggest running the code from Powershell ISE so you can step through the code by highlighting and running each line.

The solution first sources the LibraryLinkedServer functions and filters i.e. loads the functions into our current Powershell session. Next we define our list of logins t removing from linked server mappings in an array called $logins. The final two steps involve obtaining a list of SQL Servers from the Central Managment Server Z001SQL1 and the server group PRD. Next we’ll script out the linked servers using the Backup-LinkedServer function. At this stage I would manually verify the backup files before proceeding with removing the login mappings using Remove-LinkedServerLogin.

Task #3 Remove a Linked Server

You discover linked servers in development which point to production. The linked server should be removed entirely. Your task is to remove a specific linked server named PROD1  from all development servers.  Here again we’ll use the LibraryLinkedServer functions:

. .LibraryLinkedServer.ps1
Get-CMRegisteredServer "Z001SQL1" "DEV" | Backup-LinkedServer -LinkedServer "PROD1"
Get-CMRegisteredServer "Z001SQL1" "DEV" | Remove-LinkedServer -LinkedServer "PROD1"

This task is very similar to task #2 and much of the code is the same, except here we’re specifying a linked server name rather than an array of logins. Again I would suggest manually verifying the backup files before proceeding with removing the linked server.

Note: If you use this solution make sure you test, make backups and verify.

 

{ 0 comments }

Delegated SQL Server Administration with Powershell

Providing delegated administration to groups that need to perform various security functions has always been a difficult task, but thanks to Powershell V3 (currently in CTP 1 as of this blog post) and PowerGUI we have new tools to provide a solution.

The Problem

You have groups outside of database administration that need to have the ability to manage security of SQL Server logins, users, and roles. Using the out-of-the-box SQL Server roles or permissions doesn’t quite handle all of these use cases and you want to avoid  putting these groups into the sysadmin server role on SQL Server for obvious reasons. One thing you may consider is placing these groups into the  securityadmin server role, but the problem with the securityadmin role is the lack of certain rights. The securityadmin role can only add logins to the instance and not users to databases or in turn users to database roles.  You could add your delegated security  group to every database as db_securityadmin, but this would be difficult to maintain in a highly dynamic environment with thousands of databases. There’s also a larger problem of auditing the actions your security administrators perform. Ideally you want to show every security change is related to specific documented change order. 

You could use the various server and database level permissions over the fixed role approach and in fact that is what Books Online recommends, however you’ll have the same issues of per database permissions and auditable actions to overcome. If this problem statement sounds unfamiliar to you then you probably haven’t had deal with segregation of duties and reducing administration access that has become prevalent in our post-SOX IT world.

A Solution

Looking at web-based applications which run under a service account , you’ll notice  normal users and administrators users as part of almost any applications. Neither group has direct access to the database systems they touch instead a service account is used. A simple idea which can easily be applied to administration functions all we need to do is create a web-based application, assign a service account the necessary rights to perform administration tasks and restrict access to the web-based application. What’s that? You’re not web developer? Well, neither am I. Here’s where Powershell V3 and PowerGUI can help.

Powershell V3 Delegated Administration

One of the simple, but really useful changes made to the Powershell V3 is the ability to delegate administration by setting up  runas credentials for a remoting configuration. In the CTP 1 download there’s an example script called runas.ps1 located under SamplesWindowsPowerShellDelegatedAdmin which demonstrates the functionality. This presents an interesting an idea, instead of having your security users connect to each SQL Server they could connect to a single “Proxy” server. The proxy server would then connect to various SQL Servers on their behalf.  Of course the account used for the delegated administration will need to have the necessary rights perhaps even sysadmin access. As an added bonus by using Powershell remoting the security administrator’s machine doesn’t need SQL Server tools or SMO installed. The only thing they needed is Powershell.

If you go this route you’ll want to create a distinct Session Configuration (endpoint) and ACL the configuration to the appropriate groups. You’ll l also need to create the various Powershell functions for the administration tasks on the remote server, here’s where I created a module called SqlProxy.

SQLProxy Module

SQL Server lacks Powershell coverage for security administration, so I created SqlProxy module which provides various functions for managing SQL Server logins, users and roles. In addition, because auditability is a key requirement I’ve added logging to a custom Windows Eventlog called “SqlProxy” for every function which change security settings. The module can be used in Powershell V2 or V3 with or without remoting. When used within a delegated administration setting the module will be loaded into the remote session and anyone granted access to the remote endpoint will be able to execute the SqlProxy functions. We could say our problem of providing delegated administration is solved, however unless your security admins are very adapt at Powershell you’ll probably want to provide GUI.

PowerGUI SQLProxy PowerPack

Because PowerGUI provides an easy way to create MMC-style UI’s over Powershell, I applied PowerGUI to the SQLProxy module and created the SqlProxy PowerPack. The security administrators can now use GUI-based tool without having to know Powershell. The complete solution does requires some additional setup and configuration as documented below.

Putting it Together

sqlproxydiag

Diagram courtesy of yuml.me

A security administer will use PowerGUI with the SqlProxy PowerPack installed to connect to a proxy server via Poewershell remoting. The proxy server has a session configuration with delegated credentials. The proxy server also has a SqlProxy module which is then used to connect to the various SQL Servers through SMO. Note Powershell remoting is not used between the proxy server and SQL Server. The only area where Powershell remoting is used is between the user’s machine and the proxy server.

Setup

Setup Proxy Server

Requirements:
  • Windows 7 with Sp1, Windows 2008 R2 with SP1 or Windows 8.
  • Powershell V3 CTP1 or higher
Installation

1. Install SMO or SQL Server clients tools (Express edition will work)

2, Copy the SqlProxy.psm1 module from http://poshcode.org/3040 to C:Windowssystem32WindowsPowerShellv1.0ModulesSqlProxy folder

3. Create the SqlProxy Eventlog and Source by running following command

New-EventLog -LogName SqlProxy -Source SqlProxy

4. Create the SqlProxy Session Configuration with delegated credentials

$cred = Get-Credential
Register-PSSessionConfiguration -Name "SqlProxy" -RunAsCredential $cred

5. ACL the SqlProxy session configuration to the appropriate AD groups (by default only administrators on the proxy server will have access to the remote session):

Set-PSSessionConfiguration -Name SqlProxy –ShowSecurityDescriptorUI

6. Optional: As noted on the Powershell team blog the default settings  of Powershell remoting are way too low. The low default settings are particularly problematic in a fan-in scenario like SqlProxy. You should make Powershell remoting more robust by changing the default concurrent and memory settings:

cd WSMan:\localhost\Shell
Set-Item MaxShellsPerUser 25
Set-Item MaxConcurrentUsers 25
Set-Item MaxMemoryPerShellMB 1024
 
#Do the same for the SqlProxy Session Configuration
 
cd WSMan:\localhost\Plugin\SqlProxy\Quotas
Set-Item MaxShellsPerUser 25
Set-Item MaxConcurrentUsers 25
Set-Item MaxMemoryPerShellMB 1024

Setup Client

Requirements:
  • Powershell V2 or higher (that’s right you can remote from V2 client to V3 server!)
  • PowerGUI version 3.0 or higher
Installation
  1. Download and install the SqlProxy PowerPack
  2. Right click "Connect to Server" Node
  3. Select "Shared Scripts" and change $global:SqlProxy variable to your proxy server
  4. Uncomment #-ConfigurationName "sqlproxy" in the Get-ValidSession function of Shared Scripts
  5. Save script and exit script editor
  6. Optional: Re-export the PowerPack with your customizations for reuse in your environment:
    1. Select File, PowerPack Management
    2. Highlight the SQL Security Administration PowerPack
    3. Select edit and change the PowerPack file link to a UNC share in your environment all of your users will have access to.
    4. Export the PowerPack to the UNC share.
    5. Have your colleagues install the customized PowerPack. PowerGUI has nice feature to automatically update PowerPack where you specify a file link whenever you update your customized version all of your users will get update notifications

Using SqlProxy

The SqlProxy PowerPack was written to duplicate functionality available in SQL Server Management Studio. If you’re familiar with logins, user mappings, database users and roles the interface should look familiar. I’ve posted some screen shots as an additional download on the PowerGUI SqlProxy PowerPack site.

Testing On Single Machine

The solution described in this blog post will work on a single machine running Powershell V2 with minimal configuration. The only caveat is that the delegated credentials requires v3. In the course of developing this solution I used Windows 7 with a local SQL Server instance and then would test using a 3-tier solution. A minimal setup for testing on a Windows 7 machine looks like this:

Requirements

  • Powershell V2
  • Local SQL Server instance
  • PowerGUI 3.o or higher

1. Copy the SqlProxy.psm1 module from http://poshcode.org/3040 to C:Windowssystem32WindowsPowerShellv1.0ModulesSqlProxy folder

2. Create the SqlProxy Eventlog and Source by running following command

New-EventLog -LogName SqlProxy -Source SqlProxy

3. Enable Powershell remoting. This  enables remoting but also will allow you to remote into the local machine from the local machine for testing purposes:

Enable-PSRemoting

If you’re on a non-domain machine you will need to add your localhost to the trustedhosteds as described here.

Additional Configurations

If you use this solution you may want to consider additional steps to secure the environment. You can configure a Powershell session configuration to only allow specific commands to be executed. If this is something you’d like to do I encourage you read about constraining a Powershell session. In addition PowerGUI supports a lockdown script to enable administrators to customize PowerGUI configurations for their environment. You read about PowerGUI lockdown features here.

Final Thoughts

I deployed this solution in my environment with around three dozen users. Was it successful? Well, it did allow me to remove various groups from syadmin role. My only gripe is the touch point on each workstation. Because of this you end up dealing with some inconsistencies in configurations. The touch point issue becomes more of a problem as you deal with various operating systems, PowerGUI versions and even antivirus and firewall settings (I found several instances where a client firewall would block Powershell remoting traffic through PowerGUI, but allow it from Powershell). One of the things I’m looking at is moving PowerGUI into a Citrix environment to ensure a single working configuration, but for now I’m happy with the solution. That said, looking at the general problem of delegated administration I wish there was an easier way to create web-based solutions which leverage Powershell.

{ 5 comments }

Gaining SQL Server SysAdmin Access

I’ve seen this come a few times at work and I’m sure most you have experienced something similar.

Someone or an application installs SQL Server, doesn’t grant access to the DBA group and asks for DBA support.

In SQL Server 2008 and higher the built-in local administrators group is no longer automatically part of the SQL Server sysadmin role. You should add necessary logins to the sysadmin role as part of your SQL Server installation. Not automatically granting local administrators access to SQL Server is generally a good thing, however when the SQL Server installation is done by say another application then we see issues were support groups do not have access to SQL Server even though they are local administrators on the box. In the last few months I’ve seen this scenario several times and so has Argenis Fernandez (blog|twitter) as he has a helpful blog post entitled  “Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again.”  The post describes a technique of using the Sysinternals tool PsExec to gain SQL sysadmin access to SQL Server on which you already have local administrator access. The  post also links to a documented way of starting SQL Server in single user mode in order to gain SQL Server sysadmin access (see “Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out).” And finally the post mentions, but does not demonstrate a method of using the Windows Task Scheduler. If you’re interested in the how’s and why’s this works and how different versions of SQL Servers are well, different in security settings defaults I encourage you to give the post and comments a read.

Armed with information on how to gain SQL Server administration I looked at the various options. The psexec utility is blacklisted in my environment, blocked from download and listed as an untrusted application. The approach of starting SQL Server in single user mode requires taking SQL Server down and since the application is a quasi-production system restarting SQL Server would have to be coordinated or done after hours. So, I chose to to use the Windows Scheduler trick. This should work on Windows 2003/XP and higher and I’ve tested on Windows 2008 and Windows 7. The typical UAC things apply—you’ll need to run as administrator. The script I came up with is listed below. I figured if I’m getting a server were some other group performed the installation or configuration there’s no guarantee PowerShell will be installed, so I’m going old school Windows batch file on this. It feels wrong to post a Windows batch file  instead of a Powershell script on my blog, but I think using a batch file is the best approach for the problem. To use, save the script as AddDBA.bat and see the example syntax. The script must be run locally.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
@echo off
@if "%1"=="?" goto Syntax
@if "%1"==""  goto Syntax
@if "%2"==""  goto Syntax
rem **********************************
rem Script AddDBA.bat
rem Creation Date: 10/21/2011
rem Last Modified: 10/21/2011
rem Author: Chad Miller
rem ***********************************
rem Description: Adds a Windows Account to SQL Sysadmin Role
rem Use when you have local Windows admin access but lost SQL Sysadmin access
rem ***********************************
 
@echo ************************
@echo *** ServerInstance: %1
@echo ************************
set TMPFILE=%TMP%AddDBA-%RANDOM%-%TIME:~6,5%.tmp
schtasks  /Create /TN AddDBA /SC Once /ST 12:00 ^
/TR "sqlcmd -S %1 -Q \"CREATE LOGIN [%2] FROM WINDOWS; EXEC sp_addsrvrolemember [%2],[sysadmin];\" -o \"%TMPFILE%\" -e" ^
/RU "NT AUTHORITY\SYSTEM" /F
schtasks /Run /TN AddDBA
schtasks /Query /TN AddDBA /V /FO List
rem Wait 5 seconds
PING 127.0.0.1 -n 6  >NUL
rem Display output file
type %TMPFILE%
schtasks /Delete /TN AddDBA /F
goto :EXIT
 
:Syntax
@echo Syntax: AddDBA ServerInstance WindowsGroupOrLogin
@echo Example: AddDBA Z001\SQL1 Contoso\DBAGroup
goto :EXIT
 
:EXIT
{ 0 comments }