Scripting SQL Logins, Windows Logins, group membership

I’m working with AlwaysOn Availability Groups in SQ’L 2014. One of the many interesting gotchas I have with AlwaysOn is that SQL Logins on each AG node must be created with identical SIDs, otherwise after failover the database users for those SQL Logins will be orphaned.

I discovered Bill Graziano’s post back from 2010 where he scripts out the SQL Logins (including SID and hashed password), plus the Windows logins and server role membership for SQL 2008 server. I have adapted it for SQL 2014 SP2.

Note the script was originally designed to script out the logins only if they do not exist already. I removed the ‘IF NOT EXISTS’ lines so it will script out all the SQL Logins and Windows Logins regardless of whether they exist.

USE [master]
GO

/* Object: UserDefinedFunction [dbo].[fn_hexadecimal] **/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_hexadecimal] 
(
 -- Add the parameters for the function here
 @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

 DECLARE @charvalue varchar(256)
 DECLARE @i int
 DECLARE @length int
 DECLARE @hexstring char(16)
 SELECT @charvalue = '0x'
 SELECT @i = 1
 SELECT @length = DATALENGTH (@binvalue)
 SELECT @hexstring = '0123456789ABCDEF' 
 WHILE (@i <= @length) 
 BEGIN
 DECLARE @tempint int
 DECLARE @firstint int
 DECLARE @secondint int
 SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
 SELECT @firstint = FLOOR(@tempint/16)
 SELECT @secondint = @tempint - (@firstint)
 SELECT @charvalue = @charvalue +
 SUBSTRING(@hexstring, @firstint+1, 1) +
 SUBSTRING(@hexstring, @secondint+1, 1)
 SELECT @i = @i + 1
 END
 return @charvalue

END
GO


SET NOCOUNT ON
GO
--use MASTER
GO
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Script created on ' + CAST(GETDATE() AS VARCHAR(100))
PRINT '-----------------------------------------------------------------------------'
PRINT ''
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the windows logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULTDATABASE=[' + 
 default_database_name + '], DEFAULTLANGUAGE=[usenglish]
GO

'
FROM master.sys.server_principals
where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] not like 'BUILTIN%'
and [NAME] not like 'NT AUTHORITY%'
and [name] not like '%\SQLServer%'
GO

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the SQL Logins'
PRINT '-----------------------------------------------------------------------------'
select 'CREATE LOGIN [' + [name] + '] 
 WITH PASSWORD=' + [dbo].[fn_hexadecimal](password_hash) + ' HASHED,
 SID = ' + [dbo].[fn_hexadecimal](sid) + ', 
 DEFAULTDATABASE=[' + default_database_name + '], DEFAULTLANGUAGE=[usenglish], 
 CHECKEXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECKPOLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
 ALTER LOGIN [' + [name] + ']
 WITH CHECKEXPIRATION=' + 
 CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECKPOLICY=' + 
 CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO


'
--[name], [sid] , passwordhash 
from master.sys.sql_logins 
where type_desc = 'SQL_LOGIN' 
and [name] not in ('sa', 'guest')

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Disable any logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'ALTER LOGIN [' + [name] + '] DISABLE
GO
' 
from master.sys.server_principals 
where is_disabled = 1

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Assign groups'
PRINT '-----------------------------------------------------------------------------'
select 
'EXEC master..spaddsrvrolemember @loginame = N''' + l.name + ''', @rolename = N''' + r.name + '''
GO

'
from master.sys.server_role_members rm
join master.sys.server_principals r on r.principal_id = rm.role_principal_id
join master.sys.server_principals l on l.principal_id = rm.member_principal_id
where l.[name] not in ('sa')
AND l.[name] not like 'BUILTIN%'
and l.[NAME] not like 'NT AUTHORITY%'
and l.[name] not like '%\SQLServer%'

Installing sqlcmd without SQL Server

I just ran into a need for sqlcmd without SQL Server. I’m on Windows Server 2016 Datacenter.

I installed Microsoft® ODBC Driver 11 for SQL Server – Windows, with filename  msodbcsql.msi

Installed from https://www.microsoft.com/en-us/download/details.aspx?id=36434

This is a dependency for Microsoft Command Line Utilities 11 for SQL Server, with filename MsSqlCmdLnUtils.msi

 I installed from https://www.microsoft.com/en-us/download/details.aspx?id=36433

I chose the 64-bit versions of both.

Restarted my console session and able to run sqlcmd without SQL Server!

Set up an SMTP Replay (or smarthost) in Windows 2012 R2 with PowerShell

I just set up for our servers that need to send mail, we have a smart host relay to sendgrid.

Part of the setup is done via PowerShell, part through the GUI. If you can direct me on how to configure the additional steps via PowerShell I would be keenly interested and would update this site.

Start by setting up the SMTP Server, which is managed by the IIS 6 Admin console, with these commands from an elevated PowerShell prompt:

Import-Module ServerManager 
Add-WindowsFeature SMTP-Server,Web-Mgmt-Console,WEB-WMI

Then follow the steps 2-8 on this SpiceWorks Post

I would test from every server that must send email. Here’s the PowerShell way to send a test email, using splatting by building $params as a hash table for the parameters then passing in @params. Note that values can be single quoted if they’re not expanding, meaning they’re not containing any variables.

$MyDomain = 'daveslog.com'
$MyEmailPrefix = 'dcobb'
$SMTPRelayServerName = 'SMTPSERVER1'

$params = @{
From = "testing@$MyDomain"
Subject = 'Testing SMTP relay from PowerShell'
To = "$MyEmailPrefix@$MyDomain"
SmtpServer = $SMTPRelayServerName
Verbose = $true
}
Send-MailMessage @params

I want to do the whole process via PowerShell, and hope to investigate further.

 

Install SQL Express 2014 or 2016 from PowerShell

This may be useful for some folks out there. While the documentation on SQL Books online for command line installs is extensive, the examples there are limited, and the troubleshooting steps are not described clearly.

You will need to download the SQL installer iso or files and mount or extract them, and locate the setup.exe.

For SQL 2014 Take note that:

  1. This is installing SQL Express, a /FEATURES parameter was not required. Changes will be required if using a different distribution.
  2. This creates a default instance of SQL, change MSSQLSERVER to your desired instance name.
  3. Local Windows administrators will have SQL access by default
  4. The line of PowerShell code below that defines $params has no carriage returns between the single quotes.
  5. The results of a successful or failed install can be found in the folder: “$env:ProgramFiles\Microsoft SQL Server\###\Setup Bootstrap”, where ### is 120 for SQL 2014, 130 for SQL 2016.
#Set-Location to where your SQL Setup.exe is located, then run:
$params = @'
/ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /QS /HIDECONSOLE /INDICATEPROGRESS="True" /IAcceptSQLServerLicenseTerms /SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" /SQLSYSADMINACCOUNTS="builtin\administrators" /SKIPRULES="RebootRequiredCheck" 
'@ 
Start-process .\setup.exe $params -wait

For SQL 2016, I’m experimenting with SQL 2016 Enterprise Evaluation, and this worked as well:

#Set-Location to where your SQL Setup.exe is located, then run:
$params = @'
/ACTION=Install /QS /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /HIDECONSOLE /INDICATEPROGRESS="True" /IAcceptSQLServerLicenseTerms /SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" /SQLSYSADMINACCOUNTS="builtin\administrators" /SKIPRULES="RebootRequiredCheck" 
'@ 
Start-process .\setup.exe $params -wait

Presenting PowerShell for the DBA, Part 2 – Objects and Tools – for the PASS DBA Virtual Chapter

Wednesday March 8, 2017 will be presenting online  for the PASS DBA Virtual Chapter.

The topic is PowerShell for the DBA, Part 2 – Objects and Tools

If you’d like to join us, here is the attendee link!

Here’s the link to part 1 of the talk, including videodownloadable scripts, and resources to learn more.

Link to part 2 content on the PASS DBA Virtual Group – Meeting Archive page.

Link to the event page, with video and zip of demo scripts.

Hope to see you there!

Web Deploy 3.6 installed from Web Platform Installer – Features missing

Having issues setting up Web Deploy 3.8 on IIS 7.5 for 2008R2? I feel your pain.

I was installing Web Deploy 3.6 via the Web Platform Installer (WPI) onto IIS 7 on Server 2008 R2, all seemingly goes smoothly, but the interface options in IIS manager don’t show up.

After much investigation, I go to Programs and Features, and see Web Deploy 3.6, right-click and choose change, and here’s what I see:

Web Deploy 3.6 features

Turns out WPI installs it without all the features you’d actually need to deploy software from Visual Studio! The deployment handler is not installed by default. I added these features and completed install. After reboot, still can’t deploy, get an error.

Other issues with Wb Deploy 3.6 forced me to uninstall the version installed from WebPlatform Installer and install the latest Web Deploy version directly from Microsoft site, then I was able to deploy.

More info on Web Deploy 3.6 issues on Stack Overflow

Hope this helps someone else!

Presenting online for PASS DBA Virtual Chapter

I’ll be presenting on POWERSHELL FOR THE DBA  to the PASS DBA Virtual Chapter

Sept 28, 2016, Noon Eastern Time

Abstract: This webinar introduces the SQL DBA to PowerShell and how it can be used to automate many common SQL Server tasks and monitor SQL Server metrics. You will be shown the basics of PowerShell and see examples of using it to automate and monitor SQL Server more efficiently, utilizing freely available SQL PowerShell modules, including the newly available SQLServer PowerShell module released with SQL Server Management Studio 2016, and other community SQL modules.

Registration link is here:

https://attendee.gotowebinar.com/register/7552966861613110786

Will share my powershell demo scripts and recommended PowerShell resources on my PowerShell and SQL presentation page.

My first book review! SQL Server 2014 with PowerShell v5 Cookbook

Has been a very busy year for me, haven’t posted lately, but was very excited to share this.

I’ve had the opportunity to be a technical reviewer for three PowerShell books this year!

One that I’ve really enjoyed reviewing has just been released, and the publisher Packt is offering an end of year sale for $5 eBook editions.

Donabel Santos (sqlbelle.com & @sqlbelle) is a wonderful blogger, author, trainer, and Microsoft MVP. This is her second PowerShell cookbook for SQL, and it’s an incredibly useful resource for learning the practical details of PowerShell, both in general and when working with SQL Server. All her examples come with code samples so you can leverage her scripts to get productive right away.

You can check out the book on the Packt website. It’s only $5 for a great PowerShell & SQL eBook, or $30 for the eBook and print version. It’s a phenomenal resource, and a great deal for DBAs looking to take the plunge into PowerShell, and DBAs familiar with PowerShell ready to expand their knowledge about all that’s possible with PowerShell in SQL.

Please let me know if you found her book useful, and have a Merry Christmas, Happy New Year, and enjoyable holiday season. 🙂

 

 

SQL 2016 WILL have HTML5 reports!

Very exciting news for Reporting Services users, who may have felt left out regarding all the new SQL features of late.

SQL 2016 will have a new rendering engine that will generate HTML5 reports!  You can provide feedback to Microsoft right from within SSRS development, so I imagine they will use that feedback to produce a lot of hotfixes and workarounds over the first few months after release.You can always switch back to the original rendering engine in a report if HTML5 doesn’t work out for you.

I’ll be interested to investigate how reports can be CSS-styled for functionality, for mobile presentation, and to coordinate with the  look and feel of the site in which they are presented.

More info on books online: https://msdn.microsoft.com/en-us/library/ms170438.aspx