Managing SQL Server Logins #

This section only applies to SQL Server logins. It doesn’t apply to domain users, domain groups, certificate-based logins, etc.

SQLDSC provides multiple mechanisms to make managing SQL Server logins simpler. These are focused on the SID and the password (hash).

SQL Server matches SQL Server logins to database users using the SID and not the name. A SID is a binary string whose hex representation looks like this: 0x79C4A6315F6A1942AAD4A386D816BAAC. The CREATE LOGIN statement assigns a new SID unless you specify one. This is important for database mirroring partners and Availabiality Groups. A login that exists on all servers must have identical SIDs on each server.

SIDs in Resource Blocks #

You can assign a SID in a Resource Block:

resource "login" "DSCLogin" {
	sqldsc_credential = "dsc_test_credential"
	sid = "0xCE40499199867944BB3D5C3BEC854B0F"
}

This creates a login with a specific SID.

SQLDSC Remembers SIDs #

Every time SQLDSC creates a login, it “remembers” the SID. These are stored in the “SID Map” at ./config/state/sids.sidmap. It’s a simple CSV file that stores the mapping between a login name and a SID:

new_login,0x9362e1dd280ea94c9a62e3b6f14930e2
test2,0x06c455220c1211439aa25a7a60e0b7d1

When SQLDSC next creates a login, it will use the proper SID if it finds it in the SID Map. This allows new logins to be created on Availibility Groups without specifying a SID that all have the same SID.

Setting a Password #

SQLDSC doesn’t allow a password or hashed password to be stored in clear text in a configuration file. Instead it stores the name of a SQLDSC Credential stored in the Vault. These passwords are encrypted using the Data Protection API.

resource "login" "NewLogin" {
	sqldsc_credential = "NewLogin"
}

The Vault can store passwords or password hashes. If you copy and paste a password hash into the Vault, be sure to include 0x0200 or 0x0100 at the beginning. That’s how SQLDSC knows it’s a password hash. The simplest way is to query sys.sql_logins and copy paste the password_hash column from SQL Server Management Studio. Any password hash stored int the Vault is encrypted using the DPAPI.

Getting Existing Passwords #

SQLDSC can also extract the SID and password hash from an existing login. These are placed in the SID Map and Vault respectively. That is accomplished through the sqldsc vault get command:

sqldsc vault get server_name login_name 

Currently this overwrites any existing SID Map entry or Vault entry. The Vault entry is named for the login.