credit union payday loans liverpool rating
5-5 stars based on 117 reviews
Central Liverpool Credit Union - Providing financial Services to our members Partners Credit Union Limited - Big Banks - No thanks! Partners Credit Union - Providing ethical, affordable and accessible financial Services to all our members ... Need a Personal Loan but have been declined due to Bad Credit? Better Credit has the best sources of Personal loans for people with bad credit.

Most credit unions are fortunate to receive valuable support from local politicians payday loans west valley city utah including MPs, Members of the Scottish Parliament, and Welsh Assembly.

My Claim Solved va home loan with no money down one of the UK's leading financial claim experts. You could be owed £1,000s in compensation for mis-sold PPI, Endowments and FSAVCs. Get expert advice for all your financial questions, from spending , saving and investing smartly; to tackling taxes; to buying a home; to getting the right insurance. Getting a personal loan bad credit isn't as difficult as you may think, learn how I got approved and find the best personal loan options for bad credit. famas 2017 elections sunday, august 6, 2017. mackenzie king park from 9:00 a.m. to 5:00 p.m. list of official candidates. president manuel, cesar d. (sm)

Names and titles used by Internet fake loan fraudsters.

Banking began with the first prototype banks of merchants of the ancient world santander loan 123 account which made grain loans to farmers and traders who carried goods between cities and ...

Credit union payday loans liverpool, Pay off pay day loans

SQL, Active Directory, Scripting

Credit union payday loans liverpool, Pay off pay day loans

No comments

Starting in Windows Server 2012 R2 you now have a way to upgrade a cluster to Windows 2016.  The best part is it’s not an OS upgrade, but a rebuild.  The magic is that you can join a Windows 2016 server to a Windows 2012 R2 cluster.  You can upgrade your cluster with as little as one failover and thus very little down time.  Everything stays in compatibility mode until all nodes are upgraded to Windows 2016 and then you upgrade the cluster functional level.  This is great news for those of us running FCIs or AGs.

The Steps

This is just a loose outline of the steps you will need to take.  Don’t worry the video is below!

  • Make sure Node1 is primary
  • Remove Node2 replica from AG
  • Evict Node2 from cluster
  • Install OS on Node2
  • Config NIC Cards
  • Join to Domain
  • Add cluster role
  • Add to cluster
  • Install SQL
  • Enable AlwaysOn
  • Add as replica to AG
  • Failover AG to Node2
  • Make sure Node2 is primary
  • Remove Node1 replica from AG
  • Evict Node1 from cluster
  • Install OS on Node1
  • Config NIC Cards
  • Join to domain
  • Add to cluster
  • Install SQL
  • Enable AlwaysOn
  • Add as replica to AG
  • Failover AG to Node1

Let’s Upgrade a Cluster Together

In this video I upgrade a Windows 2012 R2 cluster with a SQL 2017 AG to a Windows 2016 cluster.

Additional Resources

What’s new in failover clustering: #01 Cluster OS Rolling Upgrade

 

In this post I’ll point you to some options to sync SQL logins and then I’ll demo my favorite option in a video.  If you are using Availability Groups or Mirroring you know you need to sync SQL logins and jobs among replicas.  What are your options to sync SQL Logins and maybe even jobs?  Let’s talk about some options so you know what your choices are and then I’ll show you what I suggest.

Sync SQL Logins

Method 1

Microsoft provides a couple of stored procedures you can use.  It’s not automated, but you could automate it yourself.  Once you create the SPs and run sp_help_revlogin it gives you the scripts to run on the partner to create the logins.  It’s not the prettiest method since it was designed for a 1 time transfer, but it works.

https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

Method 2

You can use SSIS to sync SQL logins.  This method has some gotchas, but I’ll leave that to the reader.  The good news is that it is automated.

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/transfer-logins-task

Method 3

Robert Davis wrote a great script back when he published his Mirroring book.  I started to write my own and was almost done when I contacted Robert and asked if he had dealt with SQL logins since the script only handled Windows logins.  His reply was something along the lines of, “What are you talking about? Of course it handles SQL logins”.  It turns out that the publisher didn’t get the right script version published with the book.  That’s when this post from Robert with the full script was born…

Transferring Logins

I also wrote about it HERE.

This script creates a stored procedure to handle the move and also uses Linked Servers.  If you can’t have linked servers in your environment this is not a good choice for you.  However, you can create the linked server in a SQL Agent job step prior to the step for transfer and then remove it in a job step after the transfer.  It breaks the rule but it does it fast enough maybe no one will notice.

Method 4

Contained databases.  A contained database keeps the entire user authentication mechanism within the database without relying on logins that sit outside the database in Master.  You can read more about contained databases here.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

Method 5

There is an awesome set of PowerShell cmdlets out there written by MVP Chrissy LeMaire.  This method is my personal choice.  It works great and is easy to automate.  You can run it with SQLAgent or you can just use Scheduled Tasks in the OS.  The scheduled tasks method is a little cleaner, but you don’t get to see it in SQL Server.  Also if you are on a cluster and running Windows 2012 you can cluster the task scheduler as an added benefit.

Chrissy wrote this with the intent of making migrations easier, and she succeeded.  In fact, I made it a point to thank her at MVP Summit last year because it made my life insanely easier.  The advantage here is that you can automate a lot more than than just logins.  In fact you can migrate and automate pretty much anything at the server level.  Here is the link that I guarantee you are going to bookmark followed by a video demo where I show how to install and automate the syncing of logins using both the SQLAgent method and the Scheduled Tasks method.

https://dbatools.io/

SQL Agent Job Code

/*
This script creates a job to copy and sync all logins in a 3 node Availability Group test environment.
User accepts all risks.  Always test in a test environment first.
Ryan J. Adams
http://www.ryanjadams.com/go/AGSync</code>

In order for this to work you HAVE to install dbatools from http://dbatools.io
This MUST be done on all replicas.
Run this from an elevated PowerShell to install it.
Try this first
Install-Module dbatools
If that doesn't work try this
Invoke-Expression (Invoke-WebRequest -UseBasicParsing https://git.io/vn1hQ)

The last option will not make it available to the SQLAgent service account so copy it from your profile to its profile.
Copy the dbatools folder
FROM C:\Users\MyProfile\Documents\WindowsPowerShell\Modules
TO C:\Users\SQLAgentProfile\Documents\WindowsPowerShell\Modules

/***** UPDATE *****/
Copy it here instead so that all users can access it.
C:\Windows\System32\WindowsPowerShell\v1.0\Modules
/******************/

--Here is what the code does after you install DBATOOLS
This will create a function to determine if the node it is running on is currently the primary. It only creates this function on SQL2012.
It then creates a job with 3 steps. The first uses the function to determine if it is primary. If it is we continue and if not we raise an error to exit the job.
That job step is set to quit reporting success even if it fails.
Step 2 copies any logins from the partner that do not already exist including all permissions.
Step 3 sync permissions for all existing logins.

YOU NEED TO CHANGE THE TARGET DB, SOURCE INSTANCE, AND DESTINATION INSTANCE IN THE JOB STEPS
THE JOB MUST RUN UNDER AN ACCOUNT THAT IS SYSADMIN ON ALL REPLICAS. IT CAN BE THE SQL AGENT SERVICE ACCOUNT OR A PROXY ACCOUNT.

*/

:CONNECT NODE1
USE [master];
GO

IF (SELECT LEFT(CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion')),2)) = 11 --This function exists on versions above 2012 and below 2012 AGs did not exist.
--This proc alone was written by Patrick Keisler
BEGIN
IF OBJECT_ID(N'dbo.fn_hadr_is_primary_replica', N'FN') IS NOT NULL
DROP FUNCTION dbo.fn_hadr_is_primary_replica;

DECLARE @SQL nvarchar(MAX)
SET @SQL = '
CREATE FUNCTION dbo.fn_hadr_is_primary_replica (@DatabaseName SYSNAME)
RETURNS TINYINT
WITH EXECUTE AS CALLER
AS
/********************************************************************
File Name: fn_hadr_is_primary_replica.sql
Applies to: SQL Server 2012
Purpose: To return either 0, 1, 2, or -1 based on whether this
@DatabaseName is a primary or secondary replica.
Parameters: @DatabaseName - The name of the database to check.
Returns: 0 = Resolving
1 = Primary
2 = Secondary
-1 = Database does not exist
Author: Patrick Keisler
Version: 1.0.1 - 07/03/2015
Help: http://www.patrickkeisler.com/
License: Freeware
********************************************************************/
BEGIN
DECLARE @HadrRole TINYINT;
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
BEGIN
-- Return role status from sys.dm_hadr_availability_replica_states
SELECT @HadrRole = ars.role
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.databases dbs
ON ars.replica_id = dbs.replica_id
WHERE dbs.name = @DatabaseName
-- @DatabaseName exists but does not belong to an AG so return 1
IF @HadrRole IS NULL SET @HadrRole = 1
RETURN @HadrRole
END
ELSE
BEGIN
-- @DatabaseName does not exist so return -1
SET @HadrRole = -1
END
RETURN @HadrRole
END'
EXECUTE sp_executesql @SQL;
END
GO

USE [msdb];
GO

--Before we create the job we need an operator
IF NOT EXISTS(select '1' from msdb..sysoperators where name = 'DBA')
EXEC msdb.dbo.sp_add_operator @name=N'DBA',
@enabled=1,
@pager_days=0,
@email_address=N'DBA@mycompany.com';
GO

--Create the category
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name='Availability Group Sync' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name='Availability Group Sync'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

--Create the job
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Copy and Sync Logins',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description='If this replica is currently the primary it will copy and sync any new accounts. It will also sync any permissions changes. If not the primary it will raise an error.',
@category_name='Availability Group Sync',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Verify Primary Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Verify Primary',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=1,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'IF sys.fn_hadr_is_primary_replica(''App1AG_DB1'') <> 1 --If it is NOT the primary
BEGIN
RAISERROR (N''Node is not primary. Error raised to exit job gracefully. This error can be safely ignored.'',
16, -- Severity,
1) -- State
END
',
@database_name=N'master',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE1 to NODE2',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "Copy-SqlLogin -Source Node1 -Destination Node2"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE1 to NODE2',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "sync-sqlloginpermissions -source Node1 -destination Node2"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE1 to NODE3',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "Copy-SqlLogin -Source Node1 -Destination Node3"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE1 to NODE3',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "sync-sqlloginpermissions -source Node1 -destination Node3"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 7:00PM',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160805,
@active_end_date=99991231,
@active_start_time=190000,
@active_end_time=235959,
@schedule_uid=N'63580255-7f51-4cd6-ad43-eb8ebc646350'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/************************************
Now we go create the job on NODE2
*************************************/
:CONNECT NODE2
USE [master];
GO

IF (SELECT LEFT(CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion')),2)) = 11 --This function exists on versions above 2012 and below 2012 AGs did not exist.
--This proc alone was written by Patrick Keisler
BEGIN
IF OBJECT_ID(N'dbo.fn_hadr_is_primary_replica', N'FN') IS NOT NULL
DROP FUNCTION dbo.fn_hadr_is_primary_replica;

DECLARE @SQL nvarchar(MAX)
SET @SQL = '
CREATE FUNCTION dbo.fn_hadr_is_primary_replica (@DatabaseName SYSNAME)
RETURNS TINYINT
WITH EXECUTE AS CALLER
AS
/********************************************************************
File Name: fn_hadr_is_primary_replica.sql
Applies to: SQL Server 2012
Purpose: To return either 0, 1, 2, or -1 based on whether this
@DatabaseName is a primary or secondary replica.
Parameters: @DatabaseName - The name of the database to check.
Returns: 0 = Resolving
1 = Primary
2 = Secondary
-1 = Database does not exist
Author: Patrick Keisler
Version: 1.0.1 - 07/03/2015
Help: http://www.patrickkeisler.com/
License: Freeware
********************************************************************/
BEGIN
DECLARE @HadrRole TINYINT;
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
BEGIN
-- Return role status from sys.dm_hadr_availability_replica_states
SELECT @HadrRole = ars.role
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.databases dbs
ON ars.replica_id = dbs.replica_id
WHERE dbs.name = @DatabaseName
-- @DatabaseName exists but does not belong to an AG so return 1
IF @HadrRole IS NULL SET @HadrRole = 1
RETURN @HadrRole
END
ELSE
BEGIN
-- @DatabaseName does not exist so return -1
SET @HadrRole = -1
END
RETURN @HadrRole
END'
EXECUTE sp_executesql @SQL;
END
GO

USE [msdb];
GO

--Before we create the job we need an operator
IF NOT EXISTS(select '1' from msdb..sysoperators where name = 'DBA')
EXEC msdb.dbo.sp_add_operator @name=N'DBA',
@enabled=1,
@pager_days=0,
@email_address=N'DBA@mycompany.com';
GO

--Create the category
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name='Availability Group Sync' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name='Availability Group Sync'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

--Create the job
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Copy and Sync Logins',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description='If this replica is currently the primary it will copy and sync any new accounts. It will also sync any permissions changes. If not the primary it will raise an error.',
@category_name='Availability Group Sync',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Verify Primary Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Verify Primary',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=1,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'IF sys.fn_hadr_is_primary_replica(''App1AG_DB1'') <> 1 --If it is NOT the primary
BEGIN
RAISERROR (N''Node is not primary. Error raised to exit job gracefully. This error can be safely ignored.'',
16, -- Severity,
1) -- State
END
',
@database_name=N'master',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE2 to NODE1',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "Copy-SqlLogin -Source Node2 -Destination Node1"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE2 to NODE1',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "sync-sqlloginpermissions -source Node2 -destination Node1"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE2 to NODE3',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "Copy-SqlLogin -Source Node2 -Destination Node3"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE2 to NODE3',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "sync-sqlloginpermissions -source Node2 -destination Node3"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 7:00PM',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160805,
@active_end_date=99991231,
@active_start_time=190000,
@active_end_time=235959,
@schedule_uid=N'63580255-7f51-4cd6-ad43-eb8ebc646350'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/************************************
Now we go create the job on NODE3
*************************************/
:CONNECT NODE3
USE [master];
GO

IF (SELECT LEFT(CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion')),2)) = 11 --This function exists on versions above 2012 and below 2012 AGs did not exist.
BEGIN
--This proc alone was written by Patrick Keisler
IF OBJECT_ID(N'dbo.fn_hadr_is_primary_replica', N'FN') IS NOT NULL
DROP FUNCTION dbo.fn_hadr_is_primary_replica;

DECLARE @SQL nvarchar(MAX)
SET @SQL = '
CREATE FUNCTION dbo.fn_hadr_is_primary_replica (@DatabaseName SYSNAME)
RETURNS TINYINT
WITH EXECUTE AS CALLER
AS
/********************************************************************
File Name: fn_hadr_is_primary_replica.sql
Applies to: SQL Server 2012
Purpose: To return either 0, 1, 2, or -1 based on whether this
@DatabaseName is a primary or secondary replica.
Parameters: @DatabaseName - The name of the database to check.
Returns: 0 = Resolving
1 = Primary
2 = Secondary
-1 = Database does not exist
Author: Patrick Keisler
Version: 1.0.1 - 07/03/2015
Help: http://www.patrickkeisler.com/
License: Freeware
********************************************************************/
BEGIN
DECLARE @HadrRole TINYINT;
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
BEGIN
-- Return role status from sys.dm_hadr_availability_replica_states
SELECT @HadrRole = ars.role
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.databases dbs
ON ars.replica_id = dbs.replica_id
WHERE dbs.name = @DatabaseName
-- @DatabaseName exists but does not belong to an AG so return 1
IF @HadrRole IS NULL SET @HadrRole = 1
RETURN @HadrRole
END
ELSE
BEGIN
-- @DatabaseName does not exist so return -1
SET @HadrRole = -1
END
RETURN @HadrRole
END'
EXECUTE sp_executesql @SQL;
END
GO

USE [msdb];
GO

--Before we create the job we need an operator
IF NOT EXISTS(select '1' from msdb..sysoperators where name = 'DBA')
EXEC msdb.dbo.sp_add_operator @name=N'DBA',
@enabled=1,
@pager_days=0,
@email_address=N'DBA@mycompany.com';
GO

--Create the category
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name='Availability Group Sync' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name='Availability Group Sync'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

--Create the job
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Copy and Sync Logins',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description='If this replica is currently the primary it will copy and sync any new accounts. It will also sync any permissions changes. If not the primary it will raise an error.',
@category_name='Availability Group Sync',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Verify Primary Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Verify Primary',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=1,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'IF sys.fn_hadr_is_primary_replica(''App1AG_DB1'') <> 1 --If it is NOT the primary
BEGIN
RAISERROR (N''Node is not primary. Error raised to exit job gracefully. This error can be safely ignored.'',
16, -- Severity,
1) -- State
END
',
@database_name=N'master',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE3 to NODE1',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "Copy-SqlLogin -Source Node3 -Destination Node1"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE3 to NODE1',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "sync-sqlloginpermissions -source Node3 -destination Node1"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE3 to NODE2',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "Copy-SqlLogin -Source Node3 -Destination Node2"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE3 to NODE2',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command "sync-sqlloginpermissions -source Node2 -destination Node3"',
@database_name=N'master',
@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 7:00PM',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160805,
@active_end_date=99991231,
@active_start_time=190000,
@active_end_time=235959,
@schedule_uid=N'63580255-7f51-4cd6-ad43-eb8ebc646350'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Powershell Job Code


# This script creates a job to copy and sync all logins in a 3 node Availability Group test environment.
# User accepts all risks. Always test in a test environment first.
# Ryan J. Adams
# http://www.ryanjadams.com/go/AGSync

Import-Module SQLPS -DisableNameChecking
$AGName = "App1AG"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server
$AGPrimary = $Server.AvailabilityGroups[$AGName].DatabaseReplicaStates | Where-Object {$_.ReplicaRole -eq "Primary"} | Select-object -property AvailabilityReplicaServerName -First 1
#Write-Host $AGPrimary.AvailabilityReplicaServerName
#write-host $env:COMPUTERNAME
If ($AGPrimary.AvailabilityReplicaServerName -eq $env:COMPUTERNAME) {
Copy-SqlLogin -Source Node1 -Destination Node2
Sync-SqlLoginPermissions -source Node1 -destination Node2
Copy-SqlLogin -Source Node1 -Destination Node3
Sync-SqlLoginPermissions -source Node1 -destination Node3}
Else {Exit}

Buffer Pool Extension was released in SQL 2014 so it’s not new.  It is also not advertised very much, but that’s not for lack of usefulness.  RAM is far faster than SSD and certainly arm based disks, but most databases are too big to cache into RAM.  BPE allows us to extend the buffer cache by storing clean pages on an SSD drive.

In this video I show how easy it is to implement Buffer Pool Extension.  You don’t even have to reboot or take an outage to turn it on, but remember to always test!  You can find the TEST code below the video.


--Review current BPE configuration
SELECT [path], state_description, current_size_in_kb,
CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
FROM sys.dm_os_buffer_pool_extension_configuration;

--Reduce SQL Server Max memory to restrict the BP and force the use of BPE
EXEC sys.sp_configure 'show advanced options', '1'  RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'max server memory (MB)', '2000';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'show advanced options', '0'  RECONFIGURE WITH OVERRIDE;
GO
/*********** WE HAVE TO RESTART SQL SERVER FOR BPE TO SEE THE RAM CHANGE ***************/

--Enable BPE
--Go look at the file size on disk right after you run this
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (FILENAME = 'C:\Temp\BP_Extension.BPE', SIZE = 2 GB);

--Now that it is enabled we'll inspect the configuration again
SELECT [path], state_description, current_size_in_kb,
CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
FROM sys.dm_os_buffer_pool_extension_configuration;

--Try to read enough data to fill BP and start using BPE
USE AdventureworksDW2016CTP3;
GO
SELECT * FROM dbo.FactResellerSalesXL_PageCompressed;
--If the above didn't do the trick then query this table as well
--SELECT * FROM dbo.FactResellerSalesXL_CCI;

--Let's see what went to BPE.  If there are no results then go query more data.
SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],
CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
AVG(read_microsec) AS [Avg Read Time (microseconds)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767
AND is_in_bpool_extension = 1
GROUP BY DB_NAME(database_id)
ORDER BY [Buffer size(MB)] DESC;

--Turn BPE off. Go look in c:\temp to see what happens to the physical data file
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;

--Put Max Server Memory back where it was
EXEC sys.sp_configure 'show advanced options', '1'  RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'max server memory (MB)', '3500';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'show advanced options', '0'  RECONFIGURE WITH OVERRIDE;
GO

I have had several Query Store questions come in from customers lately.  Some of the questions stemmed from things read on the internet so this video is aimed at both answering the questions and debunking some false information you might find out there.  We want concrete answers to the following questions, so we’ll demonstrate them all:

  • Does Query Store track cross database queries?
  • Does Query Store track queries run from the context of another database?
  • Query Store tracks every individual query in a batch, but can you relate the query back to the originating batch?
  • Does Query Store track queries that use Linked Servers?
  • Does Query Store track queries that use OPENROWSET calls?
  • Query Store does NOT track DDL statements, so what about SELECT INTO? (You might find out if this statement is truly DDL or DML)
  • What if I use Temp Tables or Table Variables since those would probably be cross database queries?

In this video I talk about Trace Flag 1117 and how it was designed to help keep data file growth with multiple data files in TempDB consistent.  You’ll see how growth occurs if the files are disproportionate and also the effect it has on TempDB contention with SQL Server special pages (PFS, GAM, SGAM).

For more on the behavior of Trace Flag 1117 and 1118 in SQL Server 2016 CHECK HERE.

Enjoy!

I’m sure everyone remembers the movie The Matrix.  You probably also remember the scene where Morpheus asks Neo if he wants to take the red pill or the blue pill.  Almost 19 years ago I was Neo and I chose the red pill.  For me that pill was Verizon.  Back then it was WorldCom (We all know how well that worked out!), then MCI, and finally Verizon.

New Challenges

New Challenges

Now it’s time for me to try the blue pill.  Starting January 9th I am more than excited to announce that I’ll be taking a Senior Premier Field Engineer position with Microsoft.  I had a lot of offers and it was a tough choice, but this was the right move for my family and I.  The opportunities that I had were all a direct result of the SQL Server community, including this one.  It really goes to show how powerful the community is and I couldn’t be more thankful to be a part of it.  Of course this does mean that I will have to step down from the PASS Board of Directors and you can read more about that HERE.

I want to thank everyone in the community that helped and supported me.  I am truly blessed and grateful to be a part of such an amazing SQL family.

Discovery

I ran across an issue where I had just configured database mail on a new server, went to send a test email, and I waited to receive it.  I waited…and waited…and waited, but still nothing.  I started to investigate and verified that database mail was indeed enabled, Service Broker was running, and the mail queue was started and running.  I went to go look at the sysmail_allitems view to see the status of my test emails to find that the sent_status column showed them as unsent.  The only errors I could find were these:

Log: SQL Server Agent
Message
[474] Unable to refresh Database Mail profile Server Mail. (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database.
System.Data.SqlClient.SqlException: profile name is not valid at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.Td)

Log: SQL Server Agent
Message
[260] Unable to start mail session.

I spent the better part of a day trying to figure this out.  Those error messages were not exactly helpful in pointing to the root cause.  Since this was a new server that I did not build myself nor did I install SQL myself I decided to uninstall and reinstall SQL.  When I ran the install it failed and upon investigation of the install log file I found an error that pointed me toward the issue.

Error: 17182, Severity: 16, State: 1.
2016-03-23 14:49:21.08 spid12s     TDSSNIClient initialization failed with error 0x80090331, status code 0x80. Reason: Unable to initialize SSL support. The client and server cannot communicate, because they do not possess a common algorithm.

At this point I knew I had a protocol issue.  The thing to note here is that this is actually blocking the install of SQL Server and I’ll explain why that is in just a minute.

Why

You may recall something called the POODLE attack that revealed a vulnerability in SSL 3.0 and TLS 1.0.  This particular server had SSL 3.0, TLS 1.0, and TLS 1.1 disabled in the registry.  Also note that TLS 1.2 was NOT disabled.  The server was running Windows 2012 R2.  These protocols were disabled to prevent the possibility of a POODLE attack.  If you are wondering how to disable these protocols on your servers then look at Microsoft Security Advisory 3009008.  To disable them for the whole OS scroll down to the Suggested Actions section and look under the heading “Disable SSL 3.0 in Windows For Server Software”.

I also want to note that the PCI Security Standards Council pushed back the date for getting off of SSL and TLS 1.0 to June 30th, 2018.  In addition to that, it should also be noted that Microsoft’s Schannel implementation of TLS 1.0 is patched against all known vulnerabilities.

You might be wondering how SQL Server was originally installed if having the protocols disabled prevented my subsequent installation attempt.  The reason is that the registry changes to disable them was made after the original install of SQL Server.  I’m glad this error happened on my subsequent install or I might not have ever figured this out.

Database Mail Breaks

So far the only issue I have run across by disabling these protocols (post installation) is that Database Mail can’t send emails and they just queue up with only the above vague messages.  The one that says it is unable to refresh a profile is an error and the other one is a warning in the SQLAgent log.  Most people don’t have alerts raised for this and if you sent those via Database Mail, you’ll never get them anyway.  This is where I want everyone to be cautious.  If these protocols have been disabled (and you may not be aware of it) then you will not get alerts for things like failed SQLAgent jobs that could be backing up your DBs or Hard IO errors, etc…  That means you could have issues on your SQL Server and might not even know about it, because the email notification is just sitting in the queue.

The Cause

The reason this issue exists is because Database Mail does not yet use the .Net Framework 4.6.1.  That is the newest version of the Framework and the only one that supports TLS 1.1 and TLS 1.2 for SMTP.  The .NET framework version 3.5 SP1 and earlier did not provide support for applications to use TLS 1.2 as a cryptographic protocol. In the update below they enabled the usage of TLS 1.2 in the .NET Framework 3.5 SP1.

The Fix

Microsoft could have fixed this in two different ways.  The first is that Database Mail be changed to utilize the .Net Framework 4.6.1 if it is installed.  The second is that the old versions of the .Net Framework be changed to support TLS 1.2.  As mentioned above, Microsoft chose option number two and just released a fix.  Check out THIS ARTICLE to obtain the fix.  The article will help you decide if you are running a vulnerable version or not and obtain the correct fix if needed.

Let me start off with a disclaimer that these thoughts on the January PASS board meeting are my own as I do not speak on behalf of PASS.

I have been on the PASS Board of Directors for about 4 months now and we had our first in person board meeting in late January. You can go here to read the January meeting minutes.

Since then, it has been a blur of quick changes as we had a few unexpected ones.  Thomas LaRock chose not to run in the PASS Executive elections in 2015 (blog) and therefore  became the Immediate Past President.  Adam Jorgensen was elected to the President role.  James Rowland-Jones (JRJ) was elected to fill the EVP seat, which would begin in January 2016; however, he later had to resign when he accepted a position with Microsoft.  JRJ’s resignation left the remainder of JRJ’s two-year term as a PASS Board Director vacant immediately.  To fill that spot, the board needed to appoint someone to finish out the Director-at-Large term.  Argenis Fernandez was appointed and voted in by the board last December.

Things appeared to be settled and Adam had assigned portfolios. I was assigned the Programs portfolio, which largely consists of our annual Summit conference.  As for the EVP vacancy in the ExecCo, Grant Fritchey was voted in as the replacement, which he assumed January 1.  This left the remainder of his seat open, meaning the board had to appoint another person and voted on Allen White during the January Board meeting. It’s extremely unusual to have this much change in such a short amount of time.

Adam took this opportunity to make some portfolio changes that worked out extremely well because it put all of us in portfolios that we were suited to best. Argenis was moved to the SQLSaturday portfolio, which was an excellent fit for someone that knows these events well from multiple viewpoints. I was moved to the Chapters portfolio. I’ve been on the board of the North Texas SQL Server User Group for the last five years and a regional mentor for the last four, so this was a perfect fit for me, especially since this area is my passion. Lastly, Allen was assigned the Programs portfolio. I personally couldn’t be more excited to have him in this role and his experience in PASS and the industry will most certainly give this portfolio great guidance. I know some of the things he is thinking about and I can say that Programs has a bright future.

So what is coming up in the future and what plans are being made? Normally in the past every portfolio owner would present their goals and plans for the coming year at the January board meeting.   Adam, since taking on the role as President, is taking the time to look strategically at PASS’s priorities and setting a long term vision.   Each portfolio director will work with the Exec to ensure that our goals frame the organization’s strategic priorities.  As a newly elected Director, this gave me a time advantage which is helpful since a couple of my goals are a little lofty.  It gives me time to do the research and discover the probability of success with those goals. My hope was to share those with you in this post, but since they are still a little fluid you’ll have to remain in suspense. I will say that the goals are in support of increasing communication and resources, marketing, and enhancing the Regional Mentor (RM) program.

There are a lot of exciting things coming and tons of amazing ideas from the board and that just energizes me more. One of the biggest takeaways I had from the meeting was the passion of the folks currently on the board. Every idea and every decision is poured over painstakingly and that has never been more clear to me than after these board meetings.  No one is perfect and we might not get it right every time, but I can assure you that these folks always do their due diligence. Being on the board is not easy, and I have more respect for that and the folks in that room now than I already had.

I have 2 things I would like to ask of the community.

1.)  Every year at the PASS Summit I make it a point to thank every single person serving on the board for their time, commitment, and willingness to make difficult decisions that support the best interest of PASS. My first challenge for you is to do the same and thank those that have had an impact on you or the community. We are a completely volunteer-run organization and surprisingly, we don’t do this nearly enough.  A simple “Thank You!” goes a long way and encourages us all.

2.)  My second request is that if you have suggestions or changes you would like to see for Chapters or the Regional Mentor program, please send me an email. I might not be able to fix everything or accommodate every request, but I can certainly try or at the very least set the path in motion. I was invited to an RM call recently for the North Central region and I walked away with a bunch of notes, so know that my door is always open.

 

Have you ever had the need to query Active Directory right out of SQL Server using native TSQL?  There are 3 ways to accomplish this.  The first is CLR, but I won’t be covering that method.  The other two methods are OPENROWSET and OPENQUERY.  OPENROWSET requires Ad Hoc Distributed Queries to be enabled and OPENQUERY requires a linked server.  I had a fellow MVP ask me how to do this recently, but his restriction was that he could not use linked servers.  He will need to use the OPENROWSET method to avoid linked servers, but I also challenged him to consider creating the linked server, running the OPENQUERY, and then deleting it.  You can do all that in 1 piece of code and I’ll show that below.  It’s just an option worth knowing in case it fits for your environment.

In the case of my friend, they used something random for user names, but he wanted to get the friendly name.  In Active Directory the attribute he was looking at is called the sAMAccountName, but the attribute he needed was the displayName.  My examples below will only pull the displayName attribute, but you can easily add in any attribute you are looking for.  If you are not sure what attribute you are looking for or what is even available, then I suggest you download and take a look at the Softerra LDAP Browser.  It is also useful for learning how to write LDAP queries.  The last thing to be aware of is that what you see in Active Directory Users and Computers is generally not the real attribute name or it is not spelled exactly the same when referencing it programmatically via an LDAP query.  Below are some screen shots of those attributes we are dealing with in this article and their real name in AD.  You can reference this article on translating what you see in ADUC to LDAP or the real AD name.

Attributes in ADUC

Query Active Directory DisplayName
Query Active Directory SAM and UPN

In the code below, the first thing we do is enable Ad Hoc Distributed Queries so we can try out the OPENROWSET method.  The advantage to this method is not having a linked server and being able to call it directly out of TSQL.  Once we have that enabled we write our query and you’ll notice that we are essentially doing 2 queries.  The first query is the LDAP query inside the OPENROWSET function.  Once those results are returned we are using another query to get what we want from the result set.  Here is where I want you to stop and think about things.  If my LDAP query pulls back 50 attributes, or “columns” in SQL terms, and I tell it I only want 10 of them, what did I just do?  I brought back a ton of extra data over the wire for no reason because I’m not planning to use it.  What we should see here is that the columns on both SELECT statements are the same.  They do not, however, have to be in the same order.  The reason for that is because LDAP does not guarantee to return results in the same order every time.  The attribute or “column” order in your first SELECT statement determines the order of your final result set.  This gives you the opportunity to alias anything if you need to.

The second part of the code looks at using OPENQUERY.  The first thing we do is create a linked server using the Active Directory provider called ADsDSOObject.  Next we write our OPENQUERY and you’ll notice that it looks very similar to OPENROWSET, but instead of defining the connection provider in the function call we reference the linked server.  The same rule applies here about how many attributes you pull back and how many you actually plan on using, so continue to keep that in mind.  The last thing I do is delete the linked server.

Query Active Directory


--There are 2 ways to query AD from SQL Server.&amp;nbsp; The first is using OPENROWSET and the second is using OPENQUERY which requires a linked server.

/*** OPENROWSET METHOD ***/
--You have to enable Ad Hoc Distributed Queries to use OPENROWSET.&amp;nbsp; Note the OPENQUERY does NOT require this to be enabled since it uses Linked servers.

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

SELECT DisplayName
FROM OPENROWSET('ADSDSOOBJECT','adsdatasource','SELECT displayName
FROM ''LDAP://mydomainFQDN.com/ou=mySubOU,ou=myTopOU,dc=mychilddomain,dc=myTLdomain,dc=com''
WHERE objectClass =&amp;nbsp;''User'' ')

/*** OPENQUERY METHOD ***/
--Here is where we create our Linked Server connection to AD
EXEC master.dbo.sp_addlinkedserver @server = N'AD', @srvproduct=N'Active Directory Services Interface', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AD', @locallogin = NULL , @useself = N'True'
GO

--Here is the query using the above created Linked server
SELECT displayName FROM OpenQuery (AD,
'SELECT displayName
FROM ''LDAP://mydomainFQDN.com/ou=mySubOU,ou=myTopOU,dc=mychilddomain,dc=myTLdomain,dc=com''
WHERE objectClass =&amp;nbsp;''User'' ')
GO

--Here we delete our Linked Server
EXEC master.dbo.sp_dropserver @server=N'AD', @droplogins='droplogins'
GO

 

More Active Directory and SQL Related Articles

 

Yesterday Microsoft announced that they will now support SQL Server on Linux.  They are targeting mid-2017, but you can download a preview now.  I am on a technical list where I saw someone inquire about the support for Windows authentication or if SQL authentication would be the only choice.  Those of you that know me remember that Active Directory is my second strongest skill set next to SQL Server.  What you might not know is that I went through a Linux phase and up until a couple years ago I always had a Linux machine sitting next to my Windows machine.  It appears it may be time for me to get back into this world.

Back to our question at hand.  Since the preview just got released I have not had a chance to test this out with SQL Server.  However, you can use Active Directory accounts with Linux and Unix by using Samba and PAM.  I see no reason why this implementation would not also work with SQL Server on Linux, but again I have not yet had a chance to test this out.  Since I already have some written installation instructions, that’s what I am providing here.

SQL Server on Linux Authentication with Samba

    • Make sure to remove any installed binary Samba packages to start clean
      • rpm -qa | grep samba
      • rpm -e SambaPackageName
      • Do the above command for each samba package found.  If it complains about dependencies then run the command like the following
        • rpm -e – -nodeps SambaPackageName
    • Download latest Samba source code
    • Extract the source code
      • tar xvfz samba-latest.tar.gz
    • Run the configure script from the source directory to create a make file
      • ./configure –with-msdfs –with-smbwrapper –with-smbmount –with-ldap –with-ads –with-cifsmount –with-libsmbclient –with-winbind –with-krb5 –with-pam –with-acl-support 2>&1 | tee config.my.log
    • Run make from the source directory
      • make 2>&1 | tee make.my.log
    • Run make install from the source directory
      • make install 2>&1 | tee makeinstall.my.log
    • Copy the following files to your documents directory for future reference
      • config.my.log
      • make.my.log
      • makeinstall.my.log
      • config.status
        • This file contains the options that you ran the configure script with
        • Search for “with options” to find the line with your configure options
    • Add Samba executable directory to path for root and yourself
      • Add the following to the end of the PATH statement in /root/.bash_profile and /home/myuser/.bash_profile
        • :/usr/local/samba/bin:/usr/local/samba/sbin
    • Add Samba MAN pages directory to the MAN path
      • Edit /etc/man.config and add the following two lines to the end
        • MANPATH /usr/local/samba/man
        • MANPATH_MAP /usr/local/samba/bin /usr/local/samba/man
    • Copy /usr/local/samba/sbin/mount.cifs to /sbin so that you can mount with CIFS
    • Make sure to create a smb.conf file in /usr/local/samba/lib BEFORE you start Samba
      • A sample can be copied from the examples folder in the downloaded source code
      • run testparm on the smb.conf to verify correct syntax
    • To automatically start Samba upon boot
      • create a file named startsmb in /usr/local/samba/sbin with the following contents
        • #!/bin/sh
        • /usr/local/samba/sbin/smbd -D
        • /usr/local/samba/sbin/nmbd -D
        • /usr/local/samba/sbin/winbindd
      • Make it executable with chmod +x startsmb
      • Add the following line to /etc/rc.local to execute the above script upon boot
        • /usr/local/samba/sbin/startsmb
    • Edit /etc/resolv.conf and make sure it has appropriate entries for “search” and “nameserver”
      • search mydomain.com
      • nameserver 192.168.0.xxx
      • nameserver 192.168.0.xxx
    • Configure Kerberos for ADS realm and KDC
      • Edit /etc/krb5.conf and add the following to the [libdefaults] section
        • default_tgs_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • default_tkt_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • preferred_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • default_realm = MYDOMAIN.COM
        • dns_lookup_kdc = true
    • Join Samba server to the domain as a domain member
      • net ads join -U username
    • Look for and delete the following files if they exist
      • /lib/libnss_winbind.so
      • /lib/libnss_winbind.so.2
      • /lib/libnss_wins.so
      • /lib/libnss_wins.so.2
      • /lib/security/pam_winbind.so
      • /lib/security/pam_winbind.so.2
    • Copy your newly compiled winbind, wins, and pam libraries from the source
      • cp sourcecodepath/source/nsswitch/libnss_winbind.so /lib
        • chown root /lib/libnss_winbind.so
        • chgrp root /lib/libnss_winbind.so
        • chmod 755 /lib/libnss_winbind.so
      • cp sourcecodepath/source/nsswitch/libnss_wins.so /lib
        • chown root /lib/libnss_wins.so
        • chgrp root /lib/libnss_wins.so
        • chmod 755 /lib/libnss_wins.so
      • cp sourcecodepath/source/bin/pam_winbind.so /lib/security
        • chown root /lib/security/pam_winbind.so
        • chgrp root /lib/security/pam_winbind.so
        • chmod 755 /lib/security/pam_winbind.so
    • Create a symbolic link for your libnss_winbind and libnss_wins libraries
      • execute /sbin/ldconfig
      • verify that libnss_winbind.so.2 and libnss_wins.so.2 exist in the /lib directory
    • Modify /etc/nsswitch.conf to make the lines for passwd and group look like this
      • passwd: files winbind
      • group:  files winbind
    • Add the following lines to your smb.conf file in the global section
      • idmap uid = 10000-20000
      • idmap gid = 10000-20000
      • winbind enum users = no
      • winbind enum groups = no
      • winbind use default domain = yes
      • winbind nested groups = yes
      • allow trusted domains = no
      • template homedir = /home/%D/%U
      • template shell = /bin/bash
    • Stop and restart the SMBD NMDB and WINBIND processes.  You can restart them and ensure the starting order is correct by executing the /usr/local/samba/sbin/startsmb file you created earlier.

Configure PAM

  • Configure PAM to use Winbind for authentication on the local system if desired
    • Edit /etc/pam.d/system-auth and add the following lines
      • auth             sufficient    pam_winbind.so use_first_pass
      • account        sufficient    pam_winbind.so use_first_pass
      • password     sufficient    pam_winbind.so use_first_pass
      • session         required    pam_winbind.so use_first_pass
  • The finished file should look like the following
    • #%PAM-1.0
    • # This file is auto-generated.
    • # User changes will be destroyed the next time authconfig is run.
    • auth        required      pam_env.so
    • auth        sufficient    pam_unix.so nullok try_first_pass
    • auth        sufficient    pam_winbind.so use_first_pass
    • auth        requisite     pam_succeed_if.so uid >= 500 quiet
    • auth        required      pam_deny.so
    • account     required      pam_unix.so
    • account     sufficient    pam_succeed_if.so uid < 500 quiet
    • account     sufficient    pam_winbind.so use_first_pass
    • account     required      pam_permit.so
    • password    requisite     pam_cracklib.so try_first_pass retry=3
    • password    sufficient    pam_unix.so md5 shadow nullok try_first_pass use_authtok
    • password    sufficient    pam_winbind.so use_first_pass
    • password    required      pam_deny.so
    • session     optional      pam_keyinit.so revoke
    • session     required      pam_limits.so
    • session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
    • session     required      pam_unix.so
    • session     required      pam_winbind.so use_first_pass