Join Domain Script without stored credentials

Hi

Well here I am for another instalment  a script to join a server to the domain without storing any credentials on the server.

Why I might hear you ask? Well in a VMware environment, storing one of these scripts in your Windows Template will make it easy for you to execute it remotely so that it could be completed by a workflow.

Everyone stores their scripts differently. When I have scripts that I need to store on a host, my Unix background comes out as I normally have a folder located at c:\bin on a server to hold them.

As systems get smarter and you can store scripts in a script repository centrally, I still use the c:\bin folder as the execution folder for these centrally stored scripts.

For this script to run, you need to have set the executionpolicy mode to unrestricted in your powershell console (both 32bit and 64bit). If you have issues with this, I have posted an article here.

The script takes advantage of the Add-Computer powershell command found here.

As you can see in the referenced document, you could modify this script to place the computer into an OU within the domain and a lot of other options.

joinDomain.ps1

 param([string]$domain, [string]$adminUser, [string]$password)  
 $credential = New-Object System.Management.Automation.PsCredential($adminUser, (ConvertTo-SecureSTring $password -AsPlainText -Force))  
 Add-Computer -DomainName $domain -Credential $credential -passthru  

This script takes three arguments, The Domain you want to join, The Administrative User (in DOMAIN\USERNAME format) and The Administrative User Password.

All arguments are passed in as plain text.

I am not going into detail of the script as the document referenced earlier explains the functions used quite well.

Okay so I am off for now, see you in the next snippet.

Setting Power Shell Execute Modes in VM Guest Templates

Hi

Well I am slightly restructuring my posts into smaller bite sized chunks. Why? well because I wanted to re-use bits and well the easiest way to make sense was to post about smaller bits of information.

So today I am going to talk about Power Shell Execute Modes. Well not so much about them but how I have set mine up in my VMware environment to enable easy execution of scripts in my environment.

Okay so I am sure most people who use VMware know about templates so will reference this as assumed knowledge.

In my environment I currently have 3 Templates, SUSE Linux Enterprise Server 11 Service Pack 1, Windows Server 2008 R2 and Windows Server 2012.

For the Windows Servers, I set the execution policy as unrestricted both in the 64Bit and 32Bit shells.

Why? So that I don't have to sign all my scripts. Is this an issue, from my experience only if you are operating in a highly secure environment.

So how is this done? Simple:

Open each console on the Template and type:

set-executionpolicy Unrestricted

You should see something like the following

Once your done, shutdown the machine and convert back to a template and we are ready.

Well I am off now, see you in the next snippet.

Converting SQL Server SIDs to login names

In the past, whenever you used MS SQL mirroring, or log shipping, you'd get this problem when you tried to bring a database back up after a failure event. The problem was that the security principals stored in a database mapped back to security principals stored in the master database, and they did so by SID. So even if you had security principals with the same name in your master database on your fail-over node, they wouldn't match up and you wouldn't be able to log in any more.

In SQL Server 2012, Microsoft introduced the idea of contained users, which are exactly what they sound like; users exist wholly within the database in which they're defined. So when you have a fail-over event, your database is back up and accessible straight away.

Unfortunately it created an issue. It's not really SQL Server's fault. We were developing some new applications that used Entity Framework as an ORM layer. We were seeing lots of errors turning up in the SQL Server run time logs similar to:

Login failed for user 'S-1-9-3-3987763894-1196622702-682210441-1728208786.'. Reason: Could not find a login matching the name provided. [CLIENT: XXX.XXX.XXX.XXX]

Now, for our own reasons many of our contained users had the same login name (and even the same password), but we had distinct connection strings for every database and data intent. And it would appear that connections to the SQL server were trying to cross domains and access databases they didn't have access to. Sometimes connections/queries were being attempted against the master database for no apparent (deliberate, explicit in code) reason.

I'm not sure exactly where to place the blame, that's the developer's call, but it could have been explicit code bugs, or a quirk in EF's connection pooling making assumptions where the server name and the login/user names and passwords that they must be interchangeable (which in the old world they may have been). But it would be really interesting to see which logins were trespassing. For what it's worth I also saw similar behaviour in Service Bus for Windows, despite it having distinct connection strings for Management, Container, and Gateway databases - I don't think they had contained users in mind at any point when making that software, which is odd given its origins in Azure.

Anyway, what's the problem? The problem is that the SID is locked away in the sys.database_principals table as a blob, and the only place you ever see the familiar string representation (S-1-9-3-...) is in the error log. If it were a Windows user SID (S-1-5-21-...) we could map it straight to a username easy-peasy, but not for SQL SIDs.

There are T-SQL functions for translating SIDs to login names and back again but they either didn't work for me, or expected/returned the binary representation of the SID which was useless.

So I wrote some Powershell to solve my problem. And here it is:

Get-DatabasePrincipalsList.ps1

param (
        [Parameter(mandatory=$true)]
        [string]$ConnectionString
)

function Get-SIDStringFromBytes ([byte[]] $bytes) {
  ( new-object System.Security.Principal.SecurityIdentifier($bytes,0) ).ToString()
}

$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $ConnectionString
try {
 $conn.Open()
} catch {
 Write-Error "Unable to open connection to the database server."
 Exit 1
}

try {
 $conn.ChangeDatabase( "master" )
} catch {
 Write-Error "Unable to change to master database: $_"
 $conn.Close()
 Exit 1
}

$dbListQuery = "SELECT name FROM sys.databases WHERE name <> 'master'"
$sqlCommand = new-object System.Data.SqlClient.SqlCommand( $dbListQuery, $conn )

try {
 $sqlReader = $sqlCommand.ExecuteReader()

 $databaseList = @()
 while ( $sqlReader.Read() ) {
  $databaseList += $sqlReader.Item("name")
 }

 $sqlReader.Close()
} catch {
 Write-Error "Error retrieving list of databases from master: $_"
 $conn.Close()
 Exit 1
}

$databaseList

$principalsQuery = "SELECT name,type_desc,sid FROM sys.database_principals WHERE sid IS NOT NULL AND type_desc IN ('SQL_USER','WINDOWS_USER')"

$principals = @()
  
foreach ( $database in $databaseList ) {
 try {
  $conn.ChangeDatabase( $database )

  $sqlCommand = new-object System.Data.SqlClient.SqlCommand( $principalsQuery, $conn )

  $sqlReader = $sqlCommand.ExecuteReader()

  while ( $sqlReader.Read() ) {
   $principal = "" | Select Name,Database,Type,SidHex,Sid

   $principal.Name = $sqlReader.Item("name")
   $principal.Database = $database
   $principal.Type = $sqlReader.Item("type_desc")
   $principal.SidHex = $sqlReader.Item( "sid" )
   try {
    $principal.Sid = Get-SidStringFromBytes $principal.SidHex
   } catch {
    $principal.Sid = "Unknown"
   }

   $principals += $principal
  }

  $sqlReader.Close()
 } catch {
  Write-Error "Something went horribly wrong reading database principals from $database"
 }
}

$principals

$conn.Close()

It takes a single argument, which is the initial connection string. If the user you're running the script as has sa rights on the database, for example, you can invoke it like this:

.\Get-DatabasePrincipalsList.ps1 -ConnectionString "Server=MyMSSQLServer;Integrated Security=true"

Any .Net SQL data provider connection string is fair game, but the first thing the script attempts to do is change to the master database. It's going to Just Work™ if you're using an account with sa, but I'm sure there are less brutal ways to get rights; I'm not a SQL Server guy.

After that, it's going to retrieve a list of databases from the sys.databases table, then iterate through them to collect a list of principals of type SQL_USER or WINDOWS_USER. Once we've got them we invoke some System.Security.Principal.SecurityIdentifier magic on the blob containing the binary representation of the SID (which just comes back as an array of bytes, byte[]) to turn it into the string format we know and love. That's this bit:

function Get-SIDStringFromBytes ([byte[]] $bytes) {
  ( new-object System.Security.Principal.SecurityIdentifier($bytes,0) ).ToString()
}

The script outputs an array of all the security principals found, so you can Where-Object your heart out to find the one you're looking for and the database it originated in.

For me, I get stuff like (details obfuscated to protect the innocent):

Name     : someusername
Database : SomeDatabase
Type     : SQL_USER
SidHex   : {1, 5, 0, 0...}
Sid      : S-1-9-3-3574901072-1311533442-3147823503-3515760278

Name     : AppUser
Database : SomeDatabase
Type     : SQL_USER
SidHex   : {1, 5, 0, 0...}
Sid      : S-1-9-3-2165426903-1168750583-1031006359-282436966

Name     : DOMAIN\JBloggs
Database : SomeDatabase
Type     : WINDOWS_USER
SidHex   : {1, 5, 0, 0...}
Sid      : S-1-5-21-1039932387-1978834618-1912882637-2728

Exciting, isn't it?

Cleaning up the Lync contacts database

So I found myself in a situation where I needed to programmatically destroy the contact lists of every user in a Microsoft Lync database. Wipe the slate clean, if you will, and let everyone start again. This was the result of management changing their mind about pre-populating everyone's contact lists with a base set of contacts after the fact. It's hard to find information on how to do this in a supported way. In fact, I couldn't find any details at all. What I did find was a mention of calling a stored procedure on the Lync RTC database called ImpDeleteContactGroups2. The ImpDeleteContactGroups2 procedure takes a single argument of the form user@host and does two things.

1. First, it attempts to resolve the user@host input into a numerical ResourceId from the Resource table, and then
2. It calls the ImpDeleteContactGroups procedure with the numeric id

Its the ImpDeleteContactGroups proc that does all the heavy lifting, though in many ways it's a simpler proc than ImpDeleteContactGroups2. As you may have guessed from the name, all ImpDeleteContactGroups actually does is delete contact groups and the association of contacts to contact groups for the user passed in as the original argument. Once it's been run, the user will still have all their contacts associated with their account, but since there are no groups left, the contact list will appear to be empty! But strange things will happen, like Lync will (if it's configured to) populate Outlook with contact details for contacts you can't see any more. All of this leads us to the partner procedure ImpDeleteContacts2, which also has a sibling proc called ImpDeleteContacts. There are no prizes for guessing that ImpDeleteContacts2 takes a user@host style argument, resolves it into a numeric ResourceId, and then calls ImpDeleteContacts to do the actual work of cleaning up contacts. My weapon of choice for this task was PowerShell, but there's nothing stopping you from just firing off queries at the SQL database from inside SQL Server Management Studio. Consider this for an SQL only approach:

USE [rtc]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[ImpDeleteContactGroups2]
  @_Owner = N'some.user@yourdomain.com'

SELECT 'Return Value' = @return_value

EXEC @return_value = [dbo].[ImpDeleteContacts2]
  @_Owner = N'some.user@yourdomain.com'

SELECT 'Return Value' = @return_value

GO

Of course this will suck if you've got lots of users to process. I ended up going for something like this, which drives the Lync command Get-CSUser to provide a complete list of every user registered in your Lync database. NOTE: This will clear all the contact lists and all the groups from every user in your Lync database. I am not responsible if you run this without understanding that.

Import-Module 'C:\Program Files\Common Files\Microsoft Lync Server 2010\Modules\Lync\Lync.psd1' $LyncSQLServerInstance = "YOURSERVER\rtc"; $sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = "server=$LyncSQLServerInstance; Integrated Security=sspi; Database=rtc" $sqlConnection.Open() $sqlCommandDeleteGroups = new-object System.Data.SqlClient.SqlCommand("ImpDeleteContactGroups2", $sqlConnection) $sqlCommandDeleteGroups.CommandTimeout = 120 $sqlCommandDeleteGroups.CommandType = [System.Data.CommandType]::StoredProcedure $sqlCommandDeleteContacts = new-object System.Data.SqlClient.SqlCommand("ImpDeleteContacts2", $sqlConnection) $sqlCommandDeleteContacts.CommandTimeout = 120 $sqlCommandDeleteContacts.CommandType = [System.Data.CommandType]::StoredProcedure Get-CSUser |% { $_.SipAddress -replace "^sip:","" } |% { $sqlCommandDeleteGroups.Parameters.Clear() $sqlCommandDeleteGroups.Parameters.Add("@_Owner", $_) | Out-Null Write-Host "Deleting groups for: " $($sqlCommandDeleteGroups.Parameters[0].Value) $sqlCommandDeleteGroups.ExecuteNonQuery() | Out-Null $sqlCommandDeleteContacts.Parameters.Clear() $sqlCommandDeleteContacts.Parameters.Add("@_Owner", $_) | Out-Null Write-Host "Deleting contacts for: " $($sqlCommandDeleteContacts.Parameters[0].Value) $sqlCommandDeleteContacts.ExecuteNonQuery() | Out-Null }