Error 18456 a little more friendly in 2012

While preparing a demo for a presentation, I generated a 18456 error in the SQL instance by selecting a non-existent database in my connection string.

To my surprise, the SQL Errorlog now discloses the database that I was trying to connect to:

This seems to be improved for at least state 38 and state 40 of error 18456.

I no longer must create a trace to catpure the User Error Message to extract this information. Efficiency gained – now what to do with all that spare time?

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

Powershell – Adding accounts to Local Security Policy

 

One of the challenges I’ve had over the years is figuring out a way to add the SQL Service accounts to the “Perform Volume Maintenance Tasks” and “Lock Pages in Memory” local security policy privileges.

Kendra Little published a post a while back that utilized NTRights.exe. Unfortunately, this is not available for Server 2008R2 and Server Core. I have several clients on which I cannot use NTRights.exe.

I developed this function to handle this task. It takes an export of the current local security policy and evaluates it. When it finds the particular privilege you want to add, it will append the account you provided to that file and re-import that setting.

This function was written to accept pipeline and function properly with Confirm, WhatIf, and Verbose common switches. This function must be run locally on the box you are needing to modify the policy and must be run as an administrator to execute the underlying "secedit” command.

Please feel free to share any comments or suggestions you may have.

function Add-LoginToLocalPrivilege
{
<#
        .SYNOPSIS
Adds the provided login to the local security privilege that is chosen. Must be run as Administrator in UAC mode.
Returns a boolean $true if it was successful, $false if it was not.

.DESCRIPTION
Uses the built in secedit.exe to export the current configuration then re-import
the new configuration with the provided login added to the appropriate privilege.

The pipeline object must be passed in a DOMAIN\User format as string.

This function supports the -WhatIf, -Confirm, and -Verbose switches.

.PARAMETER DomainAccount
Value passed as a DOMAIN\Account format.

.PARAMETER Domain 
Domain of the account - can be local account by specifying local computer name.
Must be used in conjunction with Account.

.PARAMETER Account
Username of the account you want added to that privilege
Must be used in conjunction with Domain

.PARAMETER Privilege
The name of the privilege you want to be added.

This must be one in the following list:
SeManageVolumePrivilege
SeLockMemoryPrivilege

.PARAMETER TemporaryFolderPath
The folder path where the secedit exports and imports will reside. 

The default if this parameter is not provided is $env:USERPROFILE

.EXAMPLE
Add-LoginToLocalPrivilege -Domain "NEIER" -Account "Kyle" -Privilege "SeManageVolumePrivilege"

Using full parameter names

.EXAMPLE
Add-LoginToLocalPrivilege "NEIER\Kyle" "SeLockMemoryPrivilege"

Using Positional parameters only allowed when passing DomainAccount together, not independently.

.EXAMPLE
Add-LoginToLocalPrivilege "NEIER\Kyle" "SeLockMemoryPrivilege" -Verbose

This function supports the verbose switch. Will provide to you several 
text cues as part of the execution to the console. Will not output the text, only presents to console.

.EXAMPLE
("NEIER\Kyle", "NEIER\Stephanie") | Add-LoginToLocalPrivilege -Privilege "SeManageVolumePrivilege" -Verbose

Passing array of DOMAIN\User as pipeline parameter with -v switch for verbose logging. Only "Domain\Account"
can be passed through pipeline. You cannot use the Domain and Account parameters when using the pipeline.

.NOTES
The temporary files should be removed at the end of the script. 

If there is error - two files may remain in the $TemporaryFolderPath (default $env:USERPFORILE)
UserRightsAsTheyExist.inf
ApplyUserRights.inf

These should be deleted if they exist, but will be overwritten if this is run again.

Author:    Kyle Neier
Blog: http://sqldbamusings.blogspot.com
Twitter: Kyle_Neier
#>

    #Specify the default parameterset
    [CmdletBinding(DefaultParametersetName="JointNames", SupportsShouldProcess=$true, ConfirmImpact='High')]
param
    (
[parameter(
Mandatory=$true, 
Position=0,
ParameterSetName="SplitNames")]
[string] $Domain,

[parameter(
Mandatory=$true, 
Position=1,
ParameterSetName="SplitNames"
            )]
[string] $Account,

[parameter(
Mandatory=$true, 
Position=0,
ParameterSetName="JointNames",
ValueFromPipeline= $true
            )]
[string] $DomainAccount,

[parameter(Mandatory=$true, Position=2)]
[ValidateSet("SeManageVolumePrivilege", "SeLockMemoryPrivilege")]
[string] $Privilege,

[parameter(Mandatory=$false, Position=3)]
[string] $TemporaryFolderPath = $env:USERPROFILE
        
)

#Determine which parameter set was used
    switch ($PsCmdlet.ParameterSetName)
{
"SplitNames"
        { 
#If SplitNames was used, combine the names into a single string
            Write-Verbose "Domain and Account provided - combining for rest of script."
            $DomainAccount = "$Domain`\$Account"
        }
"JointNames"
        {
Write-Verbose "Domain\Account combination provided."
            #Need to do nothing more, the parameter passed is sufficient.
        }
}

#Created simple function here so I didn't have to re-type these commands
    function Remove-TempFiles
    {
#Evaluate whether the ApplyUserRights.inf file exists
        if(Test-Path $TemporaryFolderPath\ApplyUserRights.inf)
{
#Remove it if it does.
            Write-Verbose "Removing $TemporaryFolderPath`\ApplyUserRights.inf"
            Remove-Item $TemporaryFolderPath\ApplyUserRights.inf -Force -WhatIf:$false
        }

#Evaluate whether the UserRightsAsTheyExists.inf file exists
        if(Test-Path $TemporaryFolderPath\UserRightsAsTheyExist.inf)
{
#Remove it if it does.
            Write-Verbose "Removing $TemporaryFolderPath\UserRightsAsTheyExist.inf"
            Remove-Item $TemporaryFolderPath\UserRightsAsTheyExist.inf -Force -WhatIf:$false
        }
}

Write-Verbose "Adding $DomainAccount to $Privilege"

    Write-Verbose "Verifying that export file does not exist."
    #Clean Up any files that may be hanging around.
    Remove-TempFiles
    
Write-Verbose "Executing secedit and sending to $TemporaryFolderPath"
    #Use secedit (built in command in windows) to export current User Rights Assignment
    $SeceditResults = secedit /export /areas USER_RIGHTS /cfg $TemporaryFolderPath\UserRightsAsTheyExist.inf

#Make certain export was successful
    if($SeceditResults[$SeceditResults.Count-2] -eq "The task has completed successfully.")
{

Write-Verbose "Secedit export was successful, proceeding to re-import"
        #Save out the header of the file to be imported
        
Write-Verbose "Save out header for $TemporaryFolderPath`\ApplyUserRights.inf"
        
"[Unicode]
Unicode=yes
[Version]
signature=`"`$CHICAGO`$`"
Revision=1
[Privilege Rights]" | Out-File $TemporaryFolderPath\ApplyUserRights.inf -Force -WhatIf:$false
                                    
#Bring the exported config file in as an array
        Write-Verbose "Importing the exported secedit file."
        $SecurityPolicyExport = Get-Content $TemporaryFolderPath\UserRightsAsTheyExist.inf

        #enumerate over each of these files, looking for the Perform Volume Maintenance Tasks privilege
        [Boolean]$isFound = $false
        foreach($line in $SecurityPolicyExport)
{
if($line -like "$Privilege`*")
{
Write-Verbose "Line with the $Privilege found in export, appending $DomainAccount to it"
                            #Add the current domain\user to the list
                            $line = $line + ",$DomainAccount"
                            #output line, with all old + new accounts to re-import
                            $line | Out-File $TemporaryFolderPath\ApplyUserRights.inf -Append -WhatIf:$false
                            
$isFound = $true
            }
}

if($isFound -eq $false)
{
#If the particular command we are looking for can't be found, create it to be imported.
            Write-Verbose "No line found for $Privilege - Adding new line for $DomainAccount"
            "$Privilege`=$DomainAccount" | Out-File $TemporaryFolderPath\ApplyUserRights.inf -Append -WhatIf:$false
        }

#Import the new .inf into the local security policy.
        if ($pscmdlet.ShouldProcess($DomainAccount, "Account be added to Local Security with $Privilege privilege?"))
{
# yes, Run the import:
            Write-Verbose "Importing $TemporaryfolderPath\ApplyUserRighs.inf"
            $SeceditApplyResults = SECEDIT /configure /db secedit.sdb /cfg $TemporaryFolderPath\ApplyUserRights.inf

#Verify that update was successful (string reading, blegh.)
            if($SeceditApplyResults[$SeceditApplyResults.Count-2] -eq "The task has completed successfully.")
{
#Success, return true
                Write-Verbose "Import was successful."
                Write-Output $true
            }
else
            {
#Import failed for some reason
                Write-Verbose "Import from $TemporaryFolderPath\ApplyUserRights.inf failed."
                Write-Output $false
                Write-Error -Message "The import from$TemporaryFolderPath\ApplyUserRights using secedit failed. Full Text Below:
$SeceditApplyResults)"
            }
}
}
else
    {
#Export failed for some reason.
        Write-Verbose "Export to $TemporaryFolderPath\UserRightsAsTheyExist.inf failed."
        Write-Output $false
        Write-Error -Message "The export to $TemporaryFolderPath\UserRightsAsTheyExist.inf from secedit failed. Full Text Below:
$SeceditResults)"
        
}

Write-Verbose "Cleaning up temporary files that were created."
    #Delete the two temp files we created.
    Remove-TempFiles
    
}

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

Powershell - Working with Cluster Resources - Byte Arrays

Over the next few weeks, I plan to share some of the discoveries I've had when working with Windows 2008R2 Clusters and Powershell. I've had the pleasure of changing disk resource names, adding IP Address and Network Names (CAPs), Implementing DTC, adding File Shares, adding custom resources, and even installing SQL Server 2005 and 2008 - all with Powershell. In this series, I hope to provide the community with some of the hurdles I had to overcome and the solution I came up with.

As always, I welcome any suggestions for improvement and encourage you to share how you may have overcome these same obstacles.

The first issue I had was working with Byte Arrays. There are several cluster parameters that are byte arrays. Learning how to work with these was a small hurdle that I was able to overcome with the development of this function. This function is simple - it does little more than convert a byte array into a string array of hex values, but having the hexadecimal representation of a byte array at my finger tips has come in handy on multiple occasions.

The first opportunity I had to use this function was when obtaining the DiskID out of a cluster disk resource. This function was written to make that operation a snap, but because of its reuse-ability, I've used it (and it's changed names as it became more general) dozens of times. I tried my best to comment and to fully support the get-help cmdlet, but if you have questions, feel free to contact me on Twitter or leave a comment.

Happy PoSH!

function Convert-ByteArrayToHexString
{
<#

.SYNOPSIS
 Converts a Byte Array to a string array of the 2 character hex equivalents.
 
.DESCRIPTION
The specific purpose of this function is to be able to obtain the DiskID from 
the ClusterParameter "DiskVolumeInfo" object the DiskID, but this evolved to
be used with any object with the property of value which contains a byte array.

In all cases, it returns a string array, each member of the array representing the two
character hex conversion of the byte in the same position as provided.

.PARAMETER Value 
This parameter can be fed from the pipeline or as a commandline parameter. 

If not sent from Pipeline, the -Value parameter option can still be used.

.EXAMPLE
Get-ClusterResource "DiskResourceName" | Get-ClusterParameter DiskVolumeInfo | Convert-ByteArrayToHexString 

Description
-----------
Pipe an object with a .Value property to the function to get the hex string 
representation of the byte array

.EXAMPLE
Convert-ByteArrayToHexString -Value $DiskID[31..28])
    
Description
-----------
Return the hex string array representation of a byte array variable at 
specific positions in the array
    
.EXAMPLE
[string]::join("", (Convert-ByteArrayToHexString -Value $DiskID[35..32+37..36+39..38+40..47]))
    
Description
-----------
Take a byte array from specific positions and return a single string from the value

.NOTES
Author:     Kyle Neier
Company:    Perpetual Technologies, Inc.
Blog: http://sqldbamusings.blogspot.com
Twitter: Kyle_Neier

#>


    param
    (
        
        [parameter(
            Mandatory=$true, 
            Position=0, 
            ValueFromPipeline=$true, 
            ValueFromPipelineByPropertyName=$true)]
        [byte[]] $Value
    )
    
    process
    {
        $arrayOfBytes = $Value
        [array]$arrayOfStrings = @()
        #enumerate over each of the bytes in the array provided
        foreach($byte in $arrayOfBytes)
        {
            #Add the string representation in Hex format to the array
            $arrayOfStrings += "{0:X2}" -f $byte
        }

        #Output the array we have built which contains all of the hex strings
        Write-Output $arrayOfStrings
    }
    
}

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

T-SQL Tuesday #028 – Is it really specialization?

This is the first time I've participated in T-SQL Tuesday - I hope to do so more often...

I can recall several years ago being a jack of all trades kind of guy. I was doing development, security, server support, workstation support, database support, and even working with the physical time clocks. I was actually quite happy – I even moved my family close to the job so that I could be home more often.

While I was there, I realized something. There are a LOT of jack of all trades people out there. With this realization – I looked at the IT horizon and decided I would either specialize in Information Security or SQL Server. Both areas of specialization play to my natural personality traits – persistent pursuit of truth and knowledge combined with complex problem solving skills. For my next position, I decided I would put out my resume and whichever job I was hired for first (security or DBA) I would make that my specialization. Fortunately, I landed a full time DBA job. I have taken that role very seriously and have enjoyed the knowledge and experience gained.

That being said, the more “specialized” I get, the more I realize I’m not specialized. I’m a jack of all trades, it’s just that the trades that I’m a “jack of” are just less common. When I chose to be a database professional (or it chose me), I came to realize that there were developers, engineers, and DBAs. My desire for automation lent itself to the DBA role of those three, further specializing my career. As a DBA, I found myself doing SSIS, SSRS, SSAS, and the Database Engine. I decided to further specialize in the database engine. In the general IT space, this may seem specialized, but in the SQL Server space, this is not.

Even at this level, there is much more specialization to be had even within the database engine. There is HADR (mirroring, clustering, replication backups), Performance (indexes, OS, query tuning), Monitoring (Agent, Alerts, logs), Security (auditing, permissions), and Logical Design. I’ve met several professionals who I would consider to have been specialized even within this area as you start to dive into the storage engine, the query optimizer, memory management, connectivity, etc.

To me, specialization isn’t about finding a niche, it’s about recursion. It’s about chasing your passions to places your current peers may not travel to. Once there, it’s about recognizing where within this now reduced area of expertise you can further pursue your passions and distinguish yourself amongst the new peers only to find a whole new set of challenges and specialties to pursue.

I expect that one day, I will be forced to settle in an area whether it be by my own lack of ability to pursue the next level or by environmental factors, but until then, the journey continues.


About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter