Pablo Aizpiri

My experiences and thoughts on [mostly] technology.

Tag: SQL

AD Linked Server Query 1000 Row/Page Limit

February 25, 2016 / / 3 Comments

Sometimes, you need to dump Active Directory data into a table or view in SQL Server. It is preferable to use a programming interface to do this or tools such as PowerShell. It is easier, less problematic, provides access to array values, and you don’t have to deal with the page limit. However, sometimes you need to do this from SQL Server.

The problem doing this with SQL Server (other than not having access to multi-value properties, dealing with bitwise operations in SQL, and dealing with fragile queries) is that you normally have a row/page limit set on Active Directory of 1000. This can be changed, but it is usually not an option.

Here I provide a SQL solution to get around the 1000 page limit. It generated a temp table that can be queried after retrieving the results from AD. It is not the most elegant, but it works fairly well in only about 20 lines of code. In this case, I’m retrieving all users. Note that a limitation to this query is having over 1000 users whose name (cn) start with the same letter. Usually this is not the case, but if that’s a problem this can be a good jump start.



DECLARE @SYMBOLS AS VARCHAR(128); DECLARE @query VARCHAR(MAX); DECLARE @ADfields VARCHAR(MAX);
SET @SYMBOLS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; -- AD Search character prefixes used to partition search. (1000 page limit). Below, AD fields to retrieve:
SET @ADfields = 'cn, sAMAccountName, accountExpires, pwdLastSet, userAccountControl, ADsPath, lockouttime, manager, mail, createTimeStamp, employeeID, lastLogon, co, l';
SET @query = 'SELECT * INTO ##tmpAD FROM (';

-- Get each character and for each character construct and AD query:
WITH nmTbl AS (SELECT TOP (LEN(@SYMBOLS)) Idx = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY Idx)
SELECT @query = @query + 'SELECT * FROM OPENQUERY(ADSI, ''SELECT ' + @ADfields + ' FROM ''''LDAP://DC=newfield,DC=com''''   
WHERE objectCategory=''''Person'''' AND (cn = ''''' + SUBSTRING(@SYMBOLS, nmTbl.Idx, 1) + '*'''') AND (objectClass = ''''user'''' OR objectClass = ''''contact'''')'')
UNION
' FROM nmTbl;

-- Finish generating query string:
SELECT @query = LEFT(@query, LEN(@query) - CHARINDEX(REVERSE('UNION'), REVERSE(@query)) - 4) + ') AS qry'

-- Remove temp table if existing before running:
IF OBJECT_ID('tempdb.dbo.##tmpAD', 'U') IS NOT NULL 
	DROP TABLE ##tmpAD; 
EXECUTE(@query)

SELECT * FROM ##tmpAD

SSIS Database Security

August 27, 2015 / / 0 Comments

Generic SQL Security

Quick intro on granting SQL rights in general- I’ve found to quickly setup SQL security I normally use the “db_datareader” and “db_datawriter” roles (assuming entity needs write access) but often times I also need them to be able to execute a stored procedure as well. I don’t want to give them much more access so I find it convenient to just create a “db_executer” role I can assign:

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor

 

SSIS Security

Anyway, back to the real subject of the post, SSIS- if you have a job you want someone to run from SSIS, they need to have permissions to the SQL SSIS database. Usually I don’t want to manage individual permissions at the user level in SQL server, so a popular options is to create an AD group and grant that AD group access to the SSIS database as needed. Then one only needs to manage the AD group. More on SSIS security here.

Recently, we had a situation where we had a report that fired off a store procedure that submitted an SSIS package, waited for it to finish, and returned the results. (here’s how) We were using windows authentication which wasn’t optimal because we had to grant that group/users access to the SSIS database- and plus we had to update security in two places, the SQL SSIS database and the SSRS report.

For this situation, creating an AD group that was used by both SSRS and SSIS didn’t make sense because they would both likely diverge at some point, but mostly we didn’t want to provide those users access to log into the SQL database. So instead we created a single user account that the SSRS data source could use to connect to the SQL server- then we can just control access to the report from SSRS security. By the way, one caveat to this method is that the account can’t be SQL account- it has to be Windows authenticated account in order to run the SSIS package[s].

 

Removing SQL Users from SSIS Database

Anyways, after I did that I decided to clean up the database but found I couldn’t delete the users from the SSIS database; I got an error that dropping the user failed:

Msg 27226, Level 16, State 1, Procedure ddl_cleanup_object_permissions, Line 16
The database principal has granted or denied permissions to catalog objects in the database and cannot be dropped.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

I quickly unchecked all the database role memberships but still couldn’t drop the user. I ended up going through a series of steps with some valuable queries that I wanted to share for future debugging. First, I looked at the permissions assigned to the user:

SELECT *,
object_name(major_id) as securable,
user_name(grantor_principal_id) as grantor,
user_name(grantee_principal_id) as grantee
from sys.database_permissions
where grantee_principal_id = user_id('[DOMAIN]\[USERNAME]')

At this point this only showed the “CONNECT” priviledge, which I was able to revoked:

REVOKE CONNECT TO [DOMAIN\USERNAME]

Now as far as I could tell my user didn’t have any rights but I still got the same errors. I should have paid more attention to the error message- particularly the part about a trigger. This is a trigger in the SSIS database that fires off when dropping a user that has SSIS catalog permissions. SSIS has its own permissions layer and those permissions needed to be revoked in order to remove the user. SSIS catalog permission information on users can be found in
[SSISDB].[catalog].[explicit_object_permissions] table.
I found a handy query for querying this information and generating the SQL to revoke the permissions using
catalog.revoke_permission
here:

SELECT TOP 1000 [object_type]
       ,[object_id]
       ,[principal_id]
       ,[permission_type]
       ,[is_deny]
       ,[grantor_id]
    , 'EXEC catalog.revoke_permission @object_type=' + CAST([object_type] AS VARCHAR) 
          + ', @object_id=' + CAST([object_id] AS VARCHAR) 
          + ', @principal_id=' + CAST(principal_id AS VARCHAR) 
          + ', @permission_type=' + CAST(permission_type AS VARCHAR)
   FROM [SSISDB].[catalog].[explicit_object_permissions]
   WHERE principal_id = USER_ID('Your User')

SQL Troubleshooting Queries (2)

July 10, 2015 / / 0 Comments

Found this MSSQL query on StackOverflow for troubleshooting locks or not being able to access a database being taken offline.. posting for self-reference:

SELECT
        DB_NAME(l.resource_database_id) DB,
	l.resource_type,
	l.request_mode,
	l.request_status,
	l.request_session_id,
	r.command,
	r.status,
	r.blocking_session_id,
	r.wait_type,
	r.wait_time,
	r.wait_resource,
	request_sql_text = st.text,
	s.program_name,
	most_recent_sql_text = stc.text
FROM sys.dm_tran_locks l
LEFT JOIN sys.dm_exec_requests r
	ON l.request_session_id = r.session_id
LEFT JOIN sys.dm_exec_sessions s
	ON l.request_session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c
	ON s.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
--WHERE l.resource_database_id = DB_ID('[Database Name]')
ORDER BY request_session_id;

Some additional helpful links:
Lock Modes
Wait Types; Additional wait Type Info
Locks System Table Info

Also, connection by session:

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = [SESSION ID]

SQL Troubleshooting Queries (Locks)

July 2, 2015 / / 0 Comments

Some helpful queries used to do lock/running query diagnosis.

sp_who and sp_who2 system stored procedures returns some helpful information for diagnosing current connections; but it’s hard to filter. Here’s a quick query for dumping the results into a temp table for filtering. The key field is “status” to help you determine if the connection is hung.

DECLARE @Table TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

DELETE FROM @Table
INSERT INTO @Table EXEC sp_who2

SELECT  *
FROM    @Table
WHERE DBName --...

In my case, we discovered it was a SQL Server Agent connection and it was running a job that was hanging. Some additional helpful queries to answer What is the Job Id? What are the current job statuses?

SELECT *
FROM msdb..sysjobs s 
LEFT JOIN master.sys.syslogins l 
      ONs.owner_sid = l.sid

EXEC msdb.dbo.sp_help_job @Job_name = '[..]'

Stop a job:

EXEC msdb.dbo.sp_stop_job @Job_name = '[..]'

Finally, a query to request last time a stored procedure was changed:

SELECT *
FROM sys.objects
WHERE type = 'P' 
AND NAME = '[Stored Procedure Name]'

© 2023 Pablo Aizpiri

Theme by Anders Noren — Up ↑