Archive

Systems and SQL Server

I have SQL Agent Operators named ‘Alerts’ and ‘MSXOperator’.  I use the ‘Alerts’ operator for SQL Server native generated fatal errors.   Didn’t realize the MSXOperator was created on Target SQL Server Agent by the process of enlisting as Target to the Master.  Changing the ‘Alerts’ Operator from my Central Management Server worked like a charm, but the MSXOperator didn’t work.  The error message is “Cannot modify or delete operator ‘MSXOperator’ while this server is a TSX.”.  Of course I googled the error, others had the error but there were no answers.

The error is generated on the target but not the master when this statement is run.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_operator @name=N’MSXOperator’,
@enabled=1,
@pager_days=0,
@email_address=N’SQLAlerts@Firm.com’,
@pager_address=N”,
@netsend_address=N”
GO

Execute the statement on the master instance to change the email address.  In my case I was changing to a distribution group from my personal account.  Look at the Target from Manage Target Servers on the master instance.  The download instruction Operation of “6(RE_ENLIST)” is created and on the next polling will download the email address change to the target.

capture

Check the properties of MSXOperator on the target server.  It’s been changed.  When you exit the properties you will still get the error message “Cannot modify or delete operator ‘MSXOperator’ while this server is a TSX.”.  That’s one of those SQL Server twitches.

Advertisements

Many comments praise the use of adding a linked server to AD and creating a view to capture user information.  There is a 1000 record limit, so that presents an obstacle to work around.  I struggled with permissions and never got a linked server to AD to work.  I’m not a Domain Admin and have limited exposure to AD in general.  Links to those methods are below.

Using Power shellflat filesSSIS package and Agent Job I created system to capture AD information on an hourly basis.  I experimented with the Power Shell Active Directory module a lot.  Having never had access to AD, it was a whole new world learning the structure and query syntax.  I made scripts for user group membership, computers  and here is the script to capture user information.

Import-module ActiveDirectory

#————————————————————-

# User details list

#————————————————————-
$cr = “`r” #Carriage Return
$tq = “`”” #Text Qualifier
#Column Headings
“{0}{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}”-f`

(“AccountExpirationDate”+”|”),`
($tq+”AccountExpires”+$tq+”|”),`
($tq+”assistant”+$tq+”|”),`
($tq+”Deleted”+$tq+”|”),`
($tq+”Department”+$tq+”|”),`
($tq+”DistinguishedName”+$tq+”|”),`
($tq+”EmailAddress”+$tq+”|”),`
($tq+”EmployeeNumber”+$tq+”|”),`
($tq+”Enabled”+$tq+”|”),`
($tq+”extensionAttribute4″+$tq+”|”),`
($tq+”extensionAttribute5″+$tq+”|”),`
($tq+”extensionAttribute6″+$tq+”|”),`
($tq+”extensionAttribute7″+$tq+”|”),`
($tq+”extensionAttribute9″+$tq+”|”),`
($tq+”extensionAttribute11″+$tq+”|”),`
($tq+”extensionAttribute12″+$tq+”|”),`
($tq+”extensionAttribute13″+$tq+”|”),`
($tq+”extensionAttribute15″+$tq+”|”),`
($tq+”GivenName”+$tq+”|”),`
($tq+”HomePhone”+$tq+”|”),`
($tq+”Initials”+$tq+”|”),`
($tq+”LastLogonDate”+$tq+”|”),`
($tq+”Manager”+$tq+”|”),`
($tq+”MobilePhone”+$tq+”|”),`
($tq+”Modified”+$tq+”|”),`
($tq+”Name”+$tq+”|”),`
($tq+”Office”+$tq+”|”),`
($tq+”OfficePhone”+$tq+”|”),`
($tq+”SamAccountName”+$tq+”|”),`
($tq+”Surname”+$tq+”|”),`
($tq+”Title”+$tq),`
$cr|
Out-File -Width 2147483647 -Encoding ASCII -FilePath \\LG0123SQL\Process\Spool\AdUser.txt
#Data Rows
Get-ADUser -Filter * -Properties * |
ForEach-object {
$AccountExpirationDate = $_.AccountExpirationDate
$AccountExpires = $_.AccountExpires
$assistant = $_.assistant
$Deleted = $_.Deleted
$Department = $_.Department
$DistinguishedName = $_.DistinguishedName
$EmailAddress = $_.EmailAddress
$EmployeeNumber = $_.EmployeeNumber
$Enabled = $_.Enabled
$extensionAttribute4 = $_.extensionAttribute4
$extensionAttribute5 = $_.extensionAttribute5
$extensionAttribute6 = $_.extensionAttribute6
$extensionAttribute7 = $_.extensionAttribute7
$extensionAttribute9 = $_.extensionAttribute9
$extensionAttribute11 = $_.extensionAttribute11
$extensionAttribute12 = $_.extensionAttribute12
$extensionAttribute13 = $_.extensionAttribute13
$extensionAttribute15 = $_.extensionAttribute15
$GivenName = $_.GivenName
$HomePhone = $_.HomePhone
$Initials = $_.Initials
$LastLogonDate = $_.LastLogonDate
$Manager = $_.Manager
$MobilePhone = $_.MobilePhone
$Modified = $_.Modified
$Name = $_.Name
$Office = $_.Office
$OfficePhone = $_.OfficePhone
$SamAccountName = $_.SamAccountName
$Surname = $_.Surname
$Title = $_.Title
“{0}{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}”-f`
([string]$_.AccountExpirationDate+”|”),`
($tq+[string]$_.AccountExpires+$tq+”|”),`
($tq+[string]$_.assistant+$tq+”|”),`
($tq+[string]$_.Deleted+$tq+”|”),`
($tq+[string]$_.Department+$tq+”|”),`
($tq+[string]$_.DistinguishedName+$tq+”|”),`
($tq+[string]$_.EmailAddress+$tq+”|”),`
($tq+[string]$_.EmployeeNumber+$tq+”|”),`
($tq+[string]$_.Enabled+$tq+”|”),`
($tq+[string]$_.extensionAttribute4+$tq+”|”),`
($tq+[string]$_.extensionAttribute5+$tq+”|”),`
($tq+[string]$_.extensionAttribute6+$tq+”|”),`
($tq+[string]$_.extensionAttribute7+$tq+”|”),`
($tq+[string]$_.extensionAttribute9+$tq+”|”),`
($tq+[string]$_.extensionAttribute11+$tq+”|”),`
($tq+[string]$_.extensionAttribute12+$tq+”|”),`
($tq+[string]$_.extensionAttribute13+$tq+”|”),`
($tq+[string]$_.extensionAttribute15+$tq+”|”),`
($tq+[string]$_.GivenName+$tq+”|”),`
($tq+[string]$_.HomePhone+$tq+”|”),`
($tq+[string]$_.Initials+$tq+”|”),`
($tq+[string]$_.LastLogonDate+$tq+”|”),`
($tq+[string]$_.Manager+$tq+”|”),`
($tq+[string]$_.MobilePhone+$tq+”|”),`
($tq+[string]$_.Modified+$tq+”|”),`
($tq+[string]$_.Name+$tq+”|”),`
($tq+[string]$_.Office+$tq+”|”),`
($tq+[string]$_.OfficePhone+$tq+”|”),`
($tq+[string]$_.SamAccountName+$tq+”|”),`
($tq+[string]$_.Surname+$tq+”|”),`
($tq+[string]$_.Title+$tq),`
$cr
} |
Out-File -Width 2147483647 -Encoding ASCII -FilePath \\LG0123SQL\Process\Spool\AdUser.txt -append # output

The output does need to be a UNC path. Once the flat files are created then SSIS and T-sql can take it from there to update the database of users and computers.  The project is an inventory of users, computers and the software installed on their computers.  The firm users have some expensive software and the licensing must be closely tracked.

Here are the links to other discussion on pulling in AD to SQL Server.

http://codebetter.com/brendantompkins/2003/12/19/create-a-sql-server-view-of-your-ad-users

http://dbatasks.blogspot.co.uk/2013/03/getting-data-out-of-active-directory.html