Blog ala Vidar

Most of you probably know what AD (Active Directory) is, but for you who don’t, it’s a (LDAP) directory service from Microsoft. This enables you to have all user, group and policy management in one place, instead of on all your servers. Most of the systems engineers in Omega have an AD user, which is a member of the SystemsEngineers group. This group has access to the test servers most system engineers need access to. Before we started using AD, when a new guy got hired, we had to manually go in to every server he needed access to, create a local user and add him to the local administrators-group. Now we just add a new user in AD, add him (or her) as a member of the SystemsEngineers group and he is now able to log on to all the servers he needs to, with the same username and password. Neat?

In AD you also got something that’s called an organization unit (OU). This is used to separate objects in the directory. For example could there be one OUs called Omega, OmegaIAT, and OmegaPS. Under these we would put the users, groups etc associated with those OUs. You can also have nested OUs, which means that in the Omega OU you can have one OU called Users, another called Groups, and a third called Servers. This is just to make things easier to find in the directory.

So, what does this got to do with AppFrame? Most of our customers, and also our selves are using AD, and it would be nice to be able to log on automatically into AppFrame without having to type your username and password, right? We already support this, in some degree. You can add a user to AppFrame just by adding his username. For example “COMPANY\User1”. But, what if you want to have a group in AD where you can add users, and those users would automatically be added in AppFrame? Here is an example of code that you would need. For this example we’ll use the domain “MyCompany.com”. Our domain admin have created a group called “AppFrameUsers” which is placed in the OU Office1, Department1. (Department1 is a sub OU of Office1).

First we need to create a linked server from SQL to AD:

EXEC master.dbo.sp_addlinkedserver
	@server = N'AD1',
	@provider=N'ADSDSOObject'

EXEC master.dbo.sp_addlinkedsrvlogin
	@rmtsrvname=N'AD1',
	@useself=N'False',
	@locallogin=N'JobLogin',
	@rmtuser=N'MyCompany\MyUser',
	@rmtpassword='MyDomainPassword'

We create a server “AD1”, where we link the login (“JobLogin”) that is going to run the script under in the job to our domain user (“MyCompany\MyUser”).

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @Domain AS NVARCHAR(20) = 'MyCompany'
DECLARE @TopDomain AS NVARCHAR(6) = 'COM'
DECLARE @OrgUnit AS NVARCHAR(200) = 'OU=Department1,OU=Office1'
DECLARE @GroupName AS NVARCHAR(200) = 'AppFrameUsers'
DECLARE @TemplateUser AS NVARCHAR(128) = 'AppFrameTemplateUser'

SET @SQL = '
	SELECT
	  givenName AS FirstName,
	  sn AS LastName,
	  mail AS EMailAddress,
	  sAMAccountName AS Username,
	  mobile AS MobileNumber
		INTO ##ADUsers
		FROM OPENQUERY(adsi3, ''SELECT givenName, sn, mail, sAMAccountName, mobile
				FROM ''''LDAP://DC=' + @Domain + ',DC=' + @TopDomain + '''''
				WHERE objectCategory= ''''Person''''
				  AND objectClass = ''''user''''
				  AND memberOf = ''''
					CN=' + @GroupName + ',' + @OrgUnit + ',
					DC=' + @Domain + ',
					DC=' + @TopDomain + ''''''')
		WHERE NOT EXISTS (SELECT *
					FROM stbl_System_Users
					WHERE Login = ''' + @Domain  + ''' + sAMAccountName)'

EXEC (@SQL)

WHILE EXISTS (SELECT * FROM ##ADUsers)
BEGIN
	SELECT TOP 1
	  @FirstName = FirstName,
	  @LastName = LastName,
	  @EMailAddress = @EMailAddress,
	  @Login = Username,
	  @MobileNumber = MobileNumber
		FROM ##ADUsers

	EXEC sstp_Security_Users_Create
		@Login = @Login,
		@FirstName = @FirstName,
		@LastName = @LastName,
		@UserEMailAddress = @EMailAddress,
		@UserSMS = @MobileNumber

	EXEC sstp_Security_Users_CopyGroupsMemberships
		@FromUser = @TemplateUser,
		@ToUser = @Login

	DELETE
		FROM ##ADUsers
		WHERE Username = @Login
END

DROP TABLE ##ADUsers

This script will create the logins that doesn’t already exist in the system and copy AppFrameTemplateUser’s permissions to the logins. Running this script every night or similar will make it much easier for our customers to add users to AppFrame (or PIMS).

NB! Please notice that the @OrgUnit is “backwards”, meaning that the top level OU is on the right. Office1->Department1 is therefore OU=Department1,OU=Office1