Querying Active Directory / LDAP with SQL


Querying Active Directory or LDAP to obtain all user account is a little tedious, and the fact that you can only get 1000 records per query makes it a little difficult. Here is a script that will query AD for every combination of the three first letters of the account name, i.e. ‘AAA*’, ‘AAB*’, ‘ABA*’, ‘CAA*’…

Just remember to add the correct domain lame in the LDAP-query and you should be fine:

SET NOCOUNT ON

DECLARE @SearchTerm varchar(10)
DECLARE @RemoteQuery varchar(2000)
DECLARE @ErrCount int = 0
DECLARE @alphabet table (
Letter char(1)
)
INSERT INTO @alphabet (Letter)
VALUES
('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('A'), ('B'), ('C'), ('D'), ('E'),
('F'), ('G'), ('H'), ('I'), ('J'), ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')


IF OBJECT_ID('tempdb..#AD_Users') IS NOT NULL
DROP TABLE #AD_Users

CREATE TABLE #AD_Users (
givenName varchar(255),
initials varchar(255),
sn varchar(255),
userPrincipalName varchar(255),
sAMAccountName varchar(255),
displayName varchar(255),
name varchar(255),
physicalDeliveryOfficeName varchar(255),
telephoneNumber varchar(255),
mail varchar(255)
)

DECLARE cur CURSOR FOR
SELECT
CONCAT(char1.Letter, char2.Letter, char3.Letter, '*')
FROM @alphabet char1
CROSS JOIN @alphabet char2
CROSS JOIN @alphabet char3
WHERE char1.Letter >= 'A'

OPEN CUR

FETCH NEXT FROM cur INTO @SearchTerm
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @RemoteQuery = '
SELECT
givenName, initials, sn, userPrincipalName, sAMAccountName, displayName, name, physicalDeliveryOfficeName, telephoneNumber, mail
FROM OPENQUERY( AD,
''SELECT givenName, initials, sn, userPrincipalName, sAMAccountName, displayName, name, physicalDeliveryOfficeName, telephoneNumber, mail
FROM ''''LDAP://domain.name''''
WHERE objectCategory = ''''User''''
AND sAMAccountName = ''''' + @SearchTerm + ''''''')'


BEGIN TRY
INSERT INTO #AD_Users
EXEC ( @RemoteQuery )
END TRY
BEGIN CATCH
SET @ErrCount = @ErrCount + 1
END CATCH

FETCH NEXT FROM cur INTO @SearchTerm
END

CLOSE cur
DEALLOCATE cur


SELECT * FROM #AD_Users

SELECT @ErrCount


 

Monitor progress of an SSIS Master package


Monitoring progress of an SSIS Master package (a package that runs other packages) is a painful process so I decided to take stab at writing a query that could pull it off by using the data found in the dbo.sysssislog tables.

For this script to work all packages must be set up with Log provider for SQL Server and it only works for “old school” package deployment mode. Output looks like this:2017-03-10 09_12_19-mRemoteNG - confCons.xml - 172.30.8.203 - Dev:

use msdb
go

DECLARE @ShowErrorMessages int = 0
DECLARE @MasterPackageName varchar(200) = 'MASTER_Stage'

DECLARE @id_min int = (SELECT MAX(id) FROM [BOS_NBDW_Config].[dbo].[sysssislog] WHERE event = 'PackageStart' AND source = @MasterPackageName)
DECLARE @id_max int = ISNULL((SELECT MAX(id) FROM [BOS_NBDW_Config].[dbo].[sysssislog] WHERE event = 'PackageEnd' AND source = @MasterPackageName AND id > @id_min), 999999999)

;WITH cte AS (
 SELECT 
 PackageStatus = CASE 
 WHEN event IN ('PackageStart','User:PackageStart') THEN '1 Running'
 WHEN event IN ('OnError','User:OnError') THEN '3 Error'
 ELSE '2 Completed'
 END,
 *
 FROM [dbo].[sysssislog]
 WHERE event in ('PackageStart','PackageEnd','User:PackageStart','User:PackageEnd', 'OnError','User:OnError')
 AND id BETWEEN @id_min AND @id_max
 )
SELECT 
 ID = MIN(ID), 
 source, 
 PackageStatus = MAX(PackageStatus),
 StartTime = MIN(StartTime),
 EndTime = CASE WHEN MAX(PackageStatus) = '1 Running' THEN NULL ELSE MAX(endtime) END,
 Elapsed = FORMAT(DATEADD(second, 
 CASE 
 WHEN MAX(PackageStatus) = '1 Running' THEN DATEDIFF(second, MIN(StartTime), GETDATE())
 ELSE DATEDIFF(second, MIN(StartTime), MAX(endtime))
 END, '1900-01-01'), 'd\d HH:mm:ss')
FROM cte
--WHERE source in (SELECT DISTINCT source FROM [dbo].[sysssislog] WHERE Event = 'PackageStart' and id >= @id)
GROUP BY source
--ORDER BY Elapsed desc
ORDER BY MIN(ID)

IF @ShowErrorMessages = 1
 SELECT DISTINCT
 [event]
 ,
 ,[starttime]
 ,endtime
 ,[message]
 FROM [BOS_NBDW_Config].[dbo].[sysssislog]
 WHERE event like '%error%'
 and id BETWEEN @id_min AND @id_max
 ORDER BY source

 

Updated: create point-in-time restore script automatically


The script I created for generating a point-in-time restore t-sql code has been updated and the last version should support sql server 2005, 2008 and 2012 versions.

There is one prerequisite:
– Only backups made to file on disk is supported

Here is the final script:

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
The script WILL NOT do any changes to your databases, it will only generate sql code 
that you need to manually execute.
 
Created by Henning Frettem, www.thefirstsql.com
*/
SET NOCOUNT ON
GO
 
DECLARE
  @DBName varchar(200),
  @PointInTime datetime,
  @Filename varchar(200),
  @tab varchar(1) = char(9),
  @cr varchar(2) = char(13)+char(10),
  @Full_BackupStartDate datetime,
  @Diff_BackupStartDate datetime,
  @Log_BackupStartDate datetime,
  @SQL varchar(max) = ''
 
SET @DBName  = 'MyDatabase'
SET @PointInTime = '2013-05-21 15:40:00'
 
BEGIN TRY
  --> Performing some checks
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName)
    RAISERROR(N'The database has never been backed up', 16, 1)
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'D' AND backup_start_date <= @PointInTime)
    RAISERROR(N'No full backup exists prior to the specified PointInTime', 16, 1)
  ELSE IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'L' AND backup_start_date > @PointInTime)
    RAISERROR(N'No backup of the log exist after the specified PointInTime, do a tail log backup first', 16, 1)
 
  --> Getting the filename and the date of the last full backup prior to the PointInTime
  SELECT TOP 1 
      @Filename = b.physical_device_name,
      @Full_BackupStartDate = backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date <= @PointInTime
  ORDER BY a.backup_start_date DESC
 
  SET @SQL = 'USE master' + @cr + 
    'GO' + @cr + @cr +
    'ALTER DATABASE ' + QUOTENAME(@DBName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' + @cr + 
    'GO' + @cr + @cr
  
  PRINT @SQL
  SET @SQL = ''
 
  --> Restore the last full backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', REPLACE, STATS=5, NORECOVERY' + @cr + 'GO' + @cr,
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date = @Full_BackupStartDate
  ORDER BY a.backup_start_date DESC   

  PRINT @SQL
  SET @SQL = ''
   
  --> Restore the last differential backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO',
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'I'
    AND a.backup_start_date > @Full_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date DESC
  
  PRINT @SQL
  SET @SQL = ''
   
  IF @Diff_BackupStartDate IS NULL
    SET @Diff_BackupStartDate = @Full_BackupStartDate
     
  --> Generate all log restores except the last one
  SELECT
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO' + @cr,
    @Log_BackupStartDate = a.backup_start_date   
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @Diff_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date
 
  PRINT @SQL
  SET @SQL = ''

  --> Generate last log restore script with the stopat command and recovery
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', RECOVERY, STOPAT = ''' + CONVERT(varchar(20), @PointInTime, 120) + '''' + @cr + 'GO' + @cr
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @PointInTime
  ORDER BY a.backup_start_date ASC
 
  PRINT @SQL
 
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
END CATCH

Error accessing SSRS Reports


I reinstalled my computer recently and as a result I had to reconfigure my Reporting Services installation as well. After configuring everything I was sort of surprised when I got this message when I tried accessing the http://localhost/Reports url: “User ‘NO-DOM\hef’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”

And it actually took me quite a few minutes (in the 30’s! yes, it’s late in the day…) to figure out the solution even though it was quite simple. By Starting the browser as an Administrator everything worked perfectly… :)

 

My first blog post…ever!


Well…I have been thinking about this for a while, and I’m not exactly sure how this is gonna end up but I’ve been wanting to have somewhere to post all my sql server related stuff. And well…TADA! Here it is :) Hopefully someone will find it useful…and if not then…hm…bummer.

I will at least post all my scripts here so please feel free to comment on both the good and the bad stuff about them. And by all means, if you have better solutions to problems or queries or whatever I post here: don’t be shy!! I’m in this to learn just as much as you are. Hopefully there will be something in it for all of us :)

– Henning