Automate Import of Active Directory Records into SQL Server

Previously we detailed how to query Active Directory with the help of a linked server in SQL Server. Now let’s take it a step further by automating the process of retrieving the records from Active Directory and storing them in a SQL Server database using a job schedule.

To automate the import and refresh of Active Directory records into a SQL Server database, do the following:

  • Setup a linked server for connection to Active Directory
  • Create a store procedure to import records from Active Directory
  • Create a SQL Server Agent job to execute the store procedure

1. Setup a linked server for connection to Active Directory

If you have not already setup a linked server in SQL Server to connect to Active Directory, then refer to Query Active Directory in SQL Server with Linked Server for detailed steps.

2. Create a store procedure to import records from Active Directory

Now let’s create a T-SQL store procedure that will import the records from Active Directory. It must do the following:

  • Check to see if the required tables exist and create them if not.
  • Remove all data from the temp AD table if exists before importing
  • Query Active Directory and insert the returned records into a temp table
  • Refresh the current live table with the newly imported data
  • If there are issues, rollback the transaction

To create the store procedure, follow these steps:

  • In Microsoft SQL Server Management Studio, click on the database where the new store procedure will reside, then click New Query
  • In the new query window, enter the T-SQL script found below.
  • Save this new store procedure as usp_<your-store-procedure-name>
  • Confirm that the store procedure was created by:
    • Expanding Programmability > Stored Procedures
-- store procedure t-sql script, save as usp_<your-store-procedure-name>
USE <your-sp-database-name>
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.usp_<your-store-procedure-name>

AS
SET NOCOUNT ON;

IF OBJECT_ID('<your-ad-table-name>temp', 'U') IS NULL
BEGIN
  CREATE TABLE dbo.<your-ad-table-name>temp
  (
    SAMAccountName nvarchar(800) NULL,
    givenName nvarchar(800) NULL,
    sn nvarchar(800) NULL,
    mail nvarchar(800) NULL,
    telephoneNumber nvarchar(800) NULL
  );
END

IF OBJECT_ID('<your-ad-table-name>', 'U') IS NULL
BEGIN
  CREATE TABLE dbo.<your-ad-table-name>
  (
    SAMAccountName nvarchar(800) NULL,
    givenName nvarchar(800) NULL,
    sn nvarchar(800) NULL,
    mail nvarchar(800) NULL,
    telephoneNumber nvarchar(800) NULL
  );
END

BEGIN TRY
  BEGIN TRANSACTION
    IF EXISTS (SELECT SAMAccountName from dbo.<your-ad-table-name>temp)
      DELETE FROM dbo.<your-ad-table-name>temp;

    INSERT INTO dbo.<your-ad-table-name>temp (SAMAccountName,givenName,sn,mail,telephoneNumber)
    SELECT * FROM OpenQuery (
      ADSI,
      'SELECT telephoneNumber,mail,sn,givenName,SAMAccountName
       FROM ''LDAP://your.domain.com/OU=<your-ou>,DC=your,DC=domain,DC=com''
       WHERE objectClass = ''user''
       AND NOT objectClass = ''computer''
      ') AS tblADS
      ORDER BY SAMAccountName

    EXEC sp_rename '<your-ad-table-name>', '<your-ad-table-name>trans'
    EXEC sp_rename '<your-ad-table-name>temp', '<your-ad-table-name>'
    EXEC sp_rename '<your-ad-table-name>trans', '<your-ad-table-name>temp'

  COMMIT TRANSACTION
END TRY

BEGIN CATCH
  ROLLBACK
END CATCH;

  

3. Create a SQL Server Agent job to execute the store procedure

Finally let’s create a SQL Server Agent job to automate the above steps. Here’s what you need to do within Microsoft SQL Server Management Studio:

  • Right click Jobs under SQL Server Agent, and select New Job…
  • Under the General page, enter the following:
    • Name
    • Owner
  • On the Steps page, click New… and enter or select the following:
    • Step name: Import Records
    • Type: Transact-SQL script (TSQL)
    • Database: master
    • Command:
      USE <your-store-procedure-database-name>
      GO
      EXEC dbo.usp_<your-store-procedure-name>
      GO
    • Click OK
  • On the Schedules page, click New… and enter or select the following:
    • Name: <Your schedule name>
    • Frequency: <Your frequency>
    • Daily Frequency : <Your daily frequency>
    • Duration: <Your duration>
    • Click OK
  • Confirm the AD table was populated with records from Active Directory after job has ran successfully as per your schedule.

Advertisements

Query Active Directory in SQL Server with Linked Server

Here’s a quick tip on how to query Active Directory in SQL Server with Linked Server using these three simple steps:

  • Ensure MS SQL Server Windows Service runs under correct account
  • Set up the Linked Server to Active Directory
  • Query Active Directory

1. Ensure MS SQL Server Windows Service runs under correct account

Launch Windows Services and confirm that MS SQL Server Windows service MSSQLSERVER is running under the Local System or an Active Directory domain account.

2. Set up the Linked Server to Active Directory

To create the linked server, do the following:

  • Launch SQL Server Management Studio (SSMS) and connect to the database from where you wish to query Active Directory
  • Expand Server Objects
  • Right click Linked Servers and select New Linked Server…
    • On the General page, enter the following:
    • Linked server: ADSI
    • Select Other data source
      • Provider: OLE DB Provider for Microsoft Directory Services
      • Product name: Active Directory Services
      • Data source: adsdatasource
      • Provider string: ADsDSOObject
  • On the Security page, enter the following
    • For a login not defined in the list above, connections will, select
      • Be made using the login’s current security context (if Active Directory server is in the same domain as the database server)
      • Be made using this security context (if Active Directory server is in a different domain to the database server), and fill in:
        • Remote login (this is the account name)
        • With password (this is the account logon password)
  • On the Server Options, use the default values
  • Click OK to create the new linked server

3, Query Active Directory

In SSMS, with the target database selected, create a new query, and use the following query to retrieve only user accounts data from Active Directory:


SELECT * FROM OpenQuery (
ADSI,
'SELECT mail, mobile, telephoneNumber, title, sn, givenName, SAMAccountName
FROM ''LDAP://OU=<your-users-ou>,DC=<your>,DC=<company>,DC=com''
WHERE objectClass = ''user''
AND NOT objectClass = ''computer''
'
) AS tblADS
ORDER BY SAMAccountName

Important note:

  • If Active Directory and database servers are in different domains, then you will need to specify the domain the LDAP string in the above query, i.e.
    FROM ”LDAP://my.domain.com/OU=<your-users-ou>,DC=<your>,DC=<domain>,DC=com”

 

How to Reorganize and Rebuild Database Indexes Using T-SQL

If you’re working with MS SQL Server 2008 databases, here are some handy tips on how to reorganize and rebuild indexes that are fragmented using T-SQL.

1. Determine Index Fragmentation

Firstly let’s determine the degree of fragmentation. We can either determine the fragmentation for all tables or a specific table in a database.

1a Determine fragmentation for all tables in a database


USE AdventureWorks2012;
GO

SELECT
  OBJECT_NAME(ips.OBJECT_ID)
  ,i.NAME ,ips.index_id ,index_type_desc
  ,avg_fragmentation_in_percent
  ,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC

1b Determine fragmentation for a specific table in a database


USE AdventureWorks2012;
GO

SELECT 
  a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'),
OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
GO

The index’s fragmentation determines whether to use reorganize or rebuild:

  • Between 5% to 30%, use reorganize
  • Greater than 30%, use rebuild

2. Reorganize a Fragmented Index

If the fragmentation is between 5 and 30 percentage, then let’s reorganize the index. We can reorganize all indexes or just a specific index within a table.

2a Reorganize all indexes in a table


-- Reorganize all indexes on the HumanResources.Employee table.
USE AdventureWorks2012;
GO

ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE ;
GO

2b Reorganize a single index in a table


-- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode
-- index on the HumanResources.Employee table.
USE AdventureWorks2012;
GO

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
ON HumanResources.Employee
REORGANIZE
GO

3 Rebuild a Fragmented Index

If the fragmentation is over 30 percentage, then let’s rebuild the index. We can rebuild all indexes or just a specific index within a table.

3a Rebuild all indexes in a table


USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO

3b Rebuild a single index in a table


USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON dbo.Employee
REBUILD;
GO

4 Potential Issues

A potential issue you may encounter is related to page level locking. This will prevent a reorganize operation on an index if page level locking is disabled. You will see this error:

The index “<index-name>” (partition 1) on table “<table-name>” cannot be reorganized because page level locking is disabled.

To get around the above error, temporary enable page level locking, perform the database index reorganize, and then disable page level locking.


-- Enable row and page level locks
USE [database-name]
GO

ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_PAGE_LOCKS = ON )
ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_ROW_LOCKS = ON )

--Disable row and page level locks
USE [database-name]
GO

ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_PAGE_LOCKS = OFF )
ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_ROW_LOCKS = OFF )

If you want to check the index setting for a particular table (i.e. to see whether page level lock is enabled or disabled), use this query:


SELECT
  Indx.name, Indx.type_desc, Indx.is_disabled,
  Indx.allow_page_locks,Indx .allow_row_locks
FROM sys.indexes Indx
LEFT OUTER JOIN sys.objects OBJ ON OBJ.object_id=Indx.Object_id
WHERE OBJ.name ='Product'

How to restore the msdb database

To ensure a successful restore of the msdb database, follow these two simple major steps:

  • Make preparations for the restore
  • Restoring the msdb database

Here are some information about the msdb database before we start:

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.

By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb. For more information, see Recovery Models (SQL Server). Notice that when SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to simple.

1 Make preparations for the restore

To prepare for a smooth restore we need carry out these tasks:

  • Get the version of the destination server
  • Get the version of source server on which the backup was created
  • Match the versions for the source and destination servers
  • Ensure exclusive access to the database

1.1 Get the version of the destination server

The versions of SQL Server need to be the same for the source and destination when restoring the msdb or model database. If the versions for the source and destination servers do not match then the restore will fail.

Use one of these methods:

SSMS Object Explorer

Connect to a SQL Server instance through SSMS and find the SQL Server version in object explorer as shown below.

SERVERPROPERTY system function

The SERVERPROPERTY system function can be used for retrieving SQL Server version information to get edition and service pack information.

Using @@Version

The SQL Server version can also be retrieved by using @@Version. It can be used in a simple select statement as shown below

1.2 Get the version of source server on which the backup was created

The best way to do this is to get the information from the backup file itself as shown below using the RESTORE HEADERONLY command.


RESTORE HEADERONLY
FROM DISK = '<backup-location>\<msdb-backup-name>.bak'
GO

1.3 Match the versions for the source and destination servers

In our case we have version 10.50.1617 which matches both the source and destination servers. If this is not the case then apply or remove service packs on the destination server to match the source server. Once both versions are the same then we are ready to go further with the process.

1.4 Ensure exclusive access to the database

Exclusive access for the database is required just like restoring any database, but for the msdb it is slightly more demanding. In the case of msdb we also have to consider the SQL Agent service. If the SQL Server agent service is running then exclusive access can not be achieved.

So for msdb we need to stop the SQL Agent service to make sure we can get exclusive access. This can be done by right clicking on SQL Server Agent and selecting Stop.

2 Restoring the msdb database

Here are the steps for restoring the msdb database:

  • Stop the SQL Server Agent
  • Restore the msdb database
  • Start the SQL Server Agent

2.1 Stop the SQL Server Agent

You can either stop the SQL Server Agent from SQL Server Configuration Manager or Control Panel > Services.

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server Agent (<instance-name>) and select Stop

2.2 Restore the msdb database

To restore the msdb database do the following:

  • Launch SQL Server Management Studio (SSMS) or activate SQLCMD from a Command Prompt
  • Issue the following:
    RESTORE DATABASE [msdb]
    FROM
    DISK = '<backup-location>\<msdb-backup-file-name>.bak'
    WITH REPLACE;
    GO
    
    -- for example
    RESTORE DATABASE [msdb]
    FROM
    DISK = 'C:\Programs\SQL Server\MSSQL\Backup\msdb_backup.bak'
    WITH REPLACE;
    GO
    
  • Click Execute (if using SSMS) or hit Enter (if using SQLCMD)

2.3 Start the SQL Server Agent

You can either start the SQL Server Agent from SQL Server Configuration Manager or Control Panel > Services.

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server Agent (<instance-name>) and select Start

Congratulations! You should’ve already successfully restored the msdb database.

How to restore the master database

Here are the steps for restoring the SQL Server master database:

  • Set MS SQL Server to Single User Mode
  • Restore master database via SQLCMD
  • Reset MS SQL Server to Multi User Mode

Before we start, here’s some important information about the master database:

The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database.

Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

Set MS SQL Server to Single User Mode

To set MS SQL Server to single user mode, do the following:

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server (<instance-name>) and select Properties
  • Click the Advanced tab and scroll to see Startup Parameters. You will see something similar to this:
    -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Append -m; to the above entry. That is you should now have something like this:
    -m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Restart SQL Server (<instance-name>)

Restore master database via SQLCMD

To restore the master database, do the following:

  • Open a Command Prompt as an administrator from the Programs menu
  • At the prompt, type SQLCMD and press Enter
  • Then type
    RESTORE DATABASE master FROM DISK = ‘backup-path\master.bak’ WITH REPLACE;
  • Prese enter and type: GO

Reset MS SQL Server to Multi User Mode

After you have successfully restore the master database, use these steps to set MS SQL Server back to multi user mode:

  • Return to the SQL Server Configuration Manager dialogue screen that you had open previously
  • Click SQL Server Services
  • Right click SQL Server (<instance-name>) and select Properties
  • Click the Advanced tab and scroll to see Startup Parameters. You will see something similar to this:
    -m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Remove -m; from the above entry. That is, you should now have something like this now: -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Restart SQL Server (<instance-name>)

That’s it! You’ve just successfully restore the master database!

Database msdb cannot be opened and marked as suspect

If you see the following message when trying to open a database in SQL Server Management Studio:

Database ‘msdb’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error:926)

Then you need to replace these two files from a backup:

  • C:\<sql-server-install-root>\MSSQL10_50.MSSQLSERVER\MSSQ\DATA\MSDBData.mdf
  • C:\<sql-server-install-root>\MSSQL10_50.MSSQLSERVER\MSSQ\DATA\MSDBLog.ldf

To do so, follow these steps:

  • Stop the MSSQLSERVER service
    • Either open Services
      • Find SQL Server (MSSQLSERVER)
      • Right click and click stop
    • Or use SQL Server Management Studio (SSMS)
      • Right the database icon and select Stop
  • Once MSSQLSERVER service has stopped, copy and replace the following files in the MSSQL\DATA\ folder:
    • MSDBData.mdf
    • MSDBLog.ldf

If the above files are backups of the current machine’s installation, then Error:926 is now fixed, and no further actions are needed.

However if no backup for the current installation was found, and a backup from another computer was used which has a different install path, then the following error will be shown:

Daily Backup – User Databases.Subplan_1′ cannot be run because the SSIS subsystem failed to load.  The job has been suspended.

Running this query:

SELECT * FROM
msdb.dbo.syssubsystems

will show that the path to the subsystem DLL are incorrect (if the current SQL Server installation is on the D drive but current records points to a different drive or location).

To fix the Job has been suspended error (due to incorrect DLL path), do the following:

  • Delete the existing entries:
    DELETE FROM msdb.dbo.syssubsystems
  • Update the table with new rows pointing to the correct location of the DLLs by:
    EXEC msdb.dbo.sp_verify_subsystems 1

How to Retrieve SQL Server Product Keys

This quick tip will come in handy when you need to find out the product keys for an installed SQL Server if that information was not recorded when it was initially installed.

Please note that this PowerShell module and its corresponding Get-SqlServerKeys command only works for MS SQL Server 2005 – 2014. It is currently not compatible with MS SQL Server 2016.

Here are the steps  you need to carry out from a desktop computer running Windows 7 or above to retrieve the SQL Server product keys.

1 Confirm PowerShell version

2 Download and install PowerShell 3.0 if required

If you have an older PowerShell version (e.g. version 1 or 2), then upgrade it to version 3. To do so, you will need to download and install Windows Management Framework 3.0 which also contains PowerShell 3.0 from this website:

https://www.microsoft.com/en-au/download/details.aspx?id=34595

3 Download the PowerShell module for retrieving the product keys

Once you’ve successfully upgraded to PowerShell version 3, please head over to this website to download the PowerShell module for retrieving SQL Server product keys:

https://gallery.technet.microsoft.com/scriptcenter/Get-SQL-Server-Product-4b5bf4f8/view/Discussions#content

4 Check Execution Policy

Before you can import and execute the PowerShell command to retrieve the SQL Server product keys, you will need to check the desktop computer’s execution policy.

If the execution policy is set to Restricted (which is the default), then temporary set it to Unrestricted. Import the PowerShell module you download in step 3, execute the command to retrieve the SQL Server product keys and then set it back to the default Restricted execution policy, that is:

  PS > Get-ExecutionPolicy
  PS > Set-ExecutionPolicy -ExecutionPolicy Unrestricted
    [Yes] Y

5 Import the PowerShell module for retrieving the product keys

Once the execution policy restriction has been lifted (i.e. temporary set to unrestricted), import the PowerShell module you download in step 3 so that the command Get-SqlServerKeys is available in the current session.

  PS > Import-Module .\Get-SqlServerKeys.psm1
    Run Once [R]

6 Run the Get-SqlServerKeys command to retrieve the product keys

Execute the Get-SqlServerKeys command to retrieve the SQL Server product keys. To retrieve SQL Server product remotely (i.e. those on a remote server), use the Server parameter, i.e.

  PS > Get-SqlServerKeys -Servers <list-of-remote-server-fqdn-or-ip-addresses>

The Servers parameter takes a list of server’s full qualify domain names (or IP addresses). If you’re on the actual server (i.e. not issuing the command from your client desktop computer), then the Servers parameter can be omitted, i.e.

  PS > Get-SqlServerKeys

Here are the commands:

And the returned information:

It will list all the SQL Server instances if there are multiple instances on the server. Please note the above assume you’re running the command from the actual server, and not remotely. If running from a client desktop computer, you will need to add the -Servers parameter and its list of servers names or IP addresses.

7 Set the Execution Policy to Restricted

Once you’re done, please don’t forget to set the execution policy back to the default Restricted.

  PS > Set-ExecutionPolicy -ExecutionPolicy Restricted