Pablo Aizpiri

My experiences and thoughts on [mostly] technology.

Exchange Dynamic Distribution Lists

This is another PowerShell script one of my coworkers came up with; it creates dynamic distribution lists. It can be handy to create distribution lists in AD based on location, for example, instead of manually managing a distribution list.


New-DynamicDistributionGroup -Name "123 Building" -RecipientFilter {(RecipientTypeDetails -eq 'UserMailbox') -and (StreetAddress -like "*123 Building*") -and (UserAccountControl -ne 'AccountDisabled, NormalAccount'))}

###

Powershell and AD User Information

Today I was handed a CSV list of AD users and asked to return the list with an “Enabled” column to represent wether a user was enabled or disabled in Active Directory. I used PowerShell to quickly whip this up. For future reference, here is the PowerShell that will take a CSV with a “User” column (that contains the user account name) and generates another CSV with the AD properties desired (in this case enabled, sam account name, and email address).


Import-CSV -Path .\mailbox_access_list.csv | ForEach-Object {
Get-ADUser ($_.User) -Properties Enabled,EmailAddress,SamAccountName | select Enabled,EmailAddress,SamAccountName
} | Export-CSV .\results.csv -NoTypeInformation

### - Pablo Aizpiri

Also, for those times when you quickly need to get users in a AD group using PowerShell…


Get-ADGroupMember [AD Group Name] | select {"[Domain Name]\" + $_.samAccountName}

### - Pablo Aizpiri

Getting emails from a group:


Get-ADGroupMember [AD Group Name] | ForEach-Object {
Get-ADUser ($_.samAccountName) -Properties EmailAddress | select EmailAddress
}

### - Pablo Aizpiri

AD Linked Server Query 1000 Row/Page Limit

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


.NET PixyCam Library

Not too long ago I saw a Charmed Lab’s PixyCam project on Kickstarter, and I decided to contribute.

PixyCam

My PixyCam assembled with Pan/Tilt Mechanism

I was really excited about all the possibilities vision can bring to the maker community. I later received the camera, and I have to admit it’s pretty cool. The PixyCam is basically a camera with a micro controller that can be used to tag and recognize colored objects. It can then send this “digested” data back to a micro controller that normally wouldn’t have the processing power to do object recognition. For example, certain colors can be tagged- “signatures”- and the camera can send back through an I2C connection the coordinates and size of one or more rectangles matching this “signature”. And at 50 FPS, there’s some pretty nifty stuff you could do with it, like a robot that follows a ball.

The camera also has two ports to connect two servos that you can control through the I2C connection as well. This makes it easy to quickly get up and going. After assembling mine with a pan/tilt mechanism, I played around with the standard demo program and had fun seeing it “look around” at whatever I had tagged for it to view. Next, I wanted to use it to try out some other ideas, but I wasn’t ready to use it with a micro controller and work on all the programming that goes along with that. Instead, I wanted to quickly use .NET to play around with it. Much to my dismay, I could not find an easy way to do this. Ironically, my desire for an “easy” route led me to build a .NET library. I wanted one and figured others could use it as well.

I’m still working on it but I put together a Visual Studio solution that includes my C# .NET PixyCam library, and the required C++/C projects (only required to generate the DLLs the .NET library uses).

I also included a sample .NET application that uses the library so that it can be easy to get started. The solution and source code can be found on GitHub: https://github.com/JhottMaster/PixyUSBdotNET/tree/master

A couple notes:

1.) Make sure you set a signature if you want the sample application to work. Just fire up PixyMon and tag a color to use as a signature.

2.) Make sure you are using a cable that works (most recommended is under 3 feet) or sends enough power to the servos. I deal with this a lot and it was super frustrating, until I got a cable that could transmit more power over further distances.

3.) The library is a work in progress, keep that in mind. Read the README file for more info.

 

 

SSIS Database Security

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')

Nobody Cares Why

The other day we had a bug in one of our applications that prevented users from using certain symbols when saving “document” objects in the application. I got a ticket about the bug, so I looked into it and realized when the application was saving the “document” object to the database it would first attempt to write the file on a filer and this is what was failing- the illegal characters were an illegal Windows file name, and when writing to the filer this was failing.

So I call up the user and shared with them the problem- when the document is being saved, the file included is also saved and the characters in the name are illegal file names. “You know- like when you save a file on your desktop and you include a backslash or something”. The user had an “Ah! Yeah, I understand” moment when it immediately made sense with their own experience of using Windows.

Anyway, later I was thinking about why did I do that? Why did I have to explain to the user why their action didn’t work?

Nobody cares why it didn’t work.

Nobody cares about some artificial (as far as the user is concerned) Windows “limitation” on how the file is “supposed” to be named. It should just work.

Usually I am pretty good about thinking from the user experience perspective; yet here I caught myself forgetting for a moment and with developer tunnel-vision. I may care about why it doesn’t work- I need to most of the time- but nobody else does, and they shouldn’t.

On a slightly unrelated note, user interfaces often suffer from developer’s view of the world. I recently heard a quote I liked regarding user interface design: “The user interface is like a joke- if you have to explain it, it’s not that good”.

Executing an SSIS package from Stored Procedure

I recently needed to do this for a report. In theory, the SSIS package was going to run on some sort of schedule, but then it was decided that the user wanted the ability to run the report on-demand. Since the SSIS package didn’t take too long to execute, I decided I would try to run it on-demand. I was using this in an SSRS report, so I had to build a stored procedure to do this.

The key here is that I had to run the SSIS package, wait until the package was done, and then return data. I also didn’t want the package to potentially run indefinitely, and so if a package ran longer than the timeout or the job ran into an issue, the report should return empty.

NOTE: Make sure the user that will run the procedure also has access to run the SSIS package. (This is granted by giving user access to SSIS database; more here)

This is what I came up with which ended up working rather nicely:

-- =============================================
-- Author:		Pablo Aizpiri
-- Create date: 7.15.2015
-- Description:	Runs the SSIS package that returns results
-- results
-- =============================================
ALTER PROCEDURE [dbo].[RefreshAndQueryFromSSIS]
	@TIMEOUT_SECONDS INT = 60 -- Max number of seconds to wait for SP to execute.
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @EXECUTION_ID BIGINT, @OUTPUT_EXECUTION_ID BIGINT

	-- Setup SSIS package to run:
	EXEC SSISDB.CATALOG.CREATE_EXECUTION
						@FOLDER_NAME = '[SSIS Package Folder Name]'
						,@PROJECT_NAME = '[SSIS Project Name'
						,@PACKAGE_NAME = '[SSIS Package .DTSX File Name]'
						,@EXECUTION_ID = @EXECUTION_ID OUTPUT

	-- Run SSIS package:
	EXEC SSISDB.CATALOG.START_EXECUTION @EXECUTION_ID
	SET @OUTPUT_EXECUTION_ID = @EXECUTION_ID;

	DECLARE @STATEID AS INT = 0, @SECONDSRAN AS INT = 0
	-- Waits until package is successfully completed or until max timeout is reached:
	WHILE (@STATEID < 3 OR @STATEID = 5) AND @SECONDSRAN < @TIMEOUT_SECONDS
	BEGIN
		WAITFOR DELAY '00:00:00:5'; -- Give time back to CPU while waiting for timeout
		SELECT @STATEID = status, @SECONDSRAN = DATEDIFF(SECOND, start_time, GETUTCDATE())
		FROM SSISDB.catalog.executions
		WHERE execution_id = @EXECUTION_ID;
	END

	SELECT *
	FROM [Results table here]
	WHERE @STATEID = 7 -- this ensures results are only returned if the package execution was successful.
END

SQL Troubleshooting Queries (2)

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)

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]'

3D Engine Update

So my pet project of writing a simple 3D engine basically was halted ever since my last “JDX Engine” post…. I’ve been busy; I got married, received a promotion, moved, and bought a house- so it’s been crazy. However, I think a big part of it was also that I let it stagnate and never came back to it. I also know I’m reinventing the wheel writing these kinds of things, and even though it’s mostly a project to learn how to deal with the challenges in the 3D arena, it can be a bit demotivating when it takes a long time to really have anything of substance to demo when you can just pick up something like Unity.

Having said that, I will always find 3D engines and their challenges intriguing. I’d love to eventually build a simple game with a 3D engine of my own design. I ended up deciding to try again now that I’m more settled and I wrote another 3D engine, mostly through the help of Frank Luna’s “3D Game Programming with DirectX 11” book. In spirit, it’s the successor to “JDX” in that it’s in C# and uses Slim DX.
I realize there’s a good chance this will be a lot like my last 3D engine project- I’ll make some progress and then I’ll get bogged down with something and it may not get worked on for months. Maybe I’ll never even touch it again. However, I enjoy building this kind of stuff, and I’m posting my code on GitHub, so maybe it will serve to help someone get started on their journey. Also open to feedback on it.
A lot like last time, it’s pretty simple. I don’t have support for the 3D Giles format like the previous engine, but I can’t even seem to find that application anymore so that’s probably for the better. I would like to find a good light mapper and then write support to import whatever its native format is so I have something that can start looking like a game. I’m thinking BSP seems most common but I haven’t decided yet.
Here’s what I do have:
  • DirectX 11 via SlimDX – most modern DirectX API.
  • Support for up to (currently) 8 dynamic lights – spot, directional, or point.
  • New framework of classes that should make using available objects much more simpler and easier.
    • Although still not full, much better support for MQO files.
    • Previous attempt only loaded triangulated meshed, this loads quads as well so you don’t have to do anything special to your mesh in Metasequoia.
    • Support for mirroring on the X-axis.
    • Loads all Metasequoia objects individually and can organize them by material type for faster rendering. (The engine doesn’t take advantage of this yet)
Here’s a few screenshot showcasing what I have so far.
Further support for MQO files:
Metasequoia’s sample “violin” (with Z-axis removed)
AH-64 Apache model
1 dynamic spot light; resource model from book.
3 dynamic spot lights

I think the main things that I need to add support for next are:

  • Sprites
  • Multi-textures.
  • Support to load some common level/map format that includes light maps
  • Optimization in rendering – this one will take a lot of work and would constantly be on-going.
    • Minimizing calls to set materials/textures
    • Scene graphs
    • Frustum Culling
« Older posts

© 2016 Pablo Aizpiri

Theme by Anders NorenUp ↑