First I want to thank Kenny McCoig for sharing with me what he’s doing to automate user account creation using powershell and Vision in his organization, his ideas and scripts gave me a great head start.

One of the more tedious and error prone processes is creating new users during on boarding. One possible solution is to use the Employee record from Deltek Vision to create the Active Directory user account. During my research I found at least 4 basic ways to do this, with multiple variations from there.

The basic options I found were to first create a powershell script for you or your team to run, which is similar to what Kenny is doing, in Kenny’s case they’re having the script emailed to them to then run, which sounds pretty efficient. The second method was to use C# or VB.net to create the accounts by using the AD modules .net, but that would need to run in web service, and that is has a fair amount of configuration on a web server. The 3rd method was to have powershell executed from some .Net code, again it would probably need to run in web service. The last method, which I chose, was to have the powershell script executed from sql code on the sql server using the cmdshell. Every method has some security issues with it, so I’ll leave that for you to consider, and point out the security items I’ve identified for this option.

Running powershell from the Sql Server cmdshell requires a few things be configured, which I’ll cover step by step in some depth. First the sql server service account needs to run under a user account that has permissions to create users in the AD OU’s you’ll be using. Second the cmd_shell needs to be enabled, then it needs a proxy account setup to run as, and finally the DeltekVision login needs access to run cmdshell. Note any account with Sysadmin or Admin in the sql roles/permission will automatically have access to cmd_shell once it’s enabled, if you’re using an account with that permission you can skip the proxy account because it will already have access. It’s definitely not recommended to have your vision account be a sysadmin, and of course other accounts would have cmd_shell as well if they’re sysadmin’s so be aware of that.

The biggest security risk from this scenario that I can think of is someone knowing your Deltek Vision login (or another sysadmin account), knowing that it has cmdshell access, and running commands under your sql service account. However your sql service account shouldn’t be an admin account, so it still should be fairly limited, but again they could create accounts if they knew the powershell. Also note that when you create users in AD and you can only add users to groups you own, so in this example the service account can’t just add someone to an admin group or any old group. If anyone has other idea, or thoughts please comment below or email me (jj@projectinsight.io)

If you’re going proceed with this approach you’ll need to first make sure you’re sql server service is running under a domain account. Note you’ll need to restart the SQL Service for the change to go into effect. That account will need the following permissions on the sql server:

Log on as a service (SeServiceLogonRight)

Replace a process-level token (SeAssignPrimaryTokenPrivilege)

Bypass traverse checking (SeChangeNotifyPrivilege)

Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

Permission to start SQL Writer

Permission to read the Event Log service

Permission to read the Remote Procedure Call service

 

FYI: WHEN TO USE DOMAIN USER ACCOUNT?
If your SQL Server interacts with other servers, services or resources on the network (ex: Files Shares, etc.) or if your SQL Server services uses linked servers to connect to other SQL Servers on the network, then you may use a low privileged domain user account for running SQL Server services. Domain user account is the most recommended account for setting up SQL Server services that interact with other servers on the network. One of the plus points of using a Domain User Account is that the account is controlled by Windows active directory therefore, domain level policy on accounts  apply to SQL Server service account as well.

 

Next you’ll need to give that account delegate permission to the OU or OU’s you setup user accounts in.

You may refer the following procedure to run the delegation:

  • Start the delegation of control wizard by performing the following steps:
    • Open Active Directory Users and Computers.
    • In the console tree, double click the domain node.
    • In the details menu, right click the organizational unit, click delegate control, and click next.
  • Select the users or group to which you want to delegate common administrative tasks. To do so, perform the following steps:
    • On the Users or Groups page, click Add.
    • In the select Users, computers or Groups, write the names of the users and groups to which you have to delegate control of the organizational unit, click OK. And click next.
  • Assign common tasks to delegate. To do so perform the following common tasks.
    • On the tasks to delgate page, click delegate the following common tasks.
    • On the tasks to delegate page, select the tasks you want to delegate, and click OK.
  • Click Finish

To delegate administrator to move user/computer objects, you can use advance mode in AD User and Computer and run delegation. It should have write privilege in both OU for the object moving. For writing new values, the administrators account should have delegated values on the user account (Full privilege in specific OU as well.

 

Now you can enable the cmdshell by running the following 5 commands. Note you’ll need to replace the service account name you created earlier with it’s password, and replace the DeltekVision account name as well.

EXEC sp_configure ‘show advanced options’, 1

GO
— To update the currently configured value for advanced options.
RECONFIGURE
GO
— To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
— To update the currently configured value for this feature.
RECONFIGURE
GO

 

Run the following Transact-SQL script to enable the Windows account to execute the xp_cmdshell extended stored procedure:

 

USE
master

GO

 

— Add a valid Windows account as proxy account.

EXEC
sp_xp_cmdshell_proxy_account
‘Domain\SQL Service account’, ‘PasswordForAccount’

GO

 

— Grant database access to the SQL Server login account that you want to provide access.

EXEC
sp_grantdbaccess
‘Replace with DeltekVision Login Account’

GO

 

— Grant execute permission on xp_cmdshell to the SQL Server login account.

GRANT
exec
ON
xp_cmdshell
TO
‘Replace with DeltekVision Login Account’

 

The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.

Once that’s complete we can create the stored procedure that we’ll use to create the accounts. There’s a few things to note. First I’m using the Firstname Lastname as the display name and username by combining them as @fullname. Second the account is enabled but set to require change at logon, and the password is ThisWillNeedToBeChanged#1

You can change the password and settings to your liking, or not have accounts enabled etc. Also note to change the OU the account is created in you’d modify the Path. You can find all of the account setup options here: https://technet.microsoft.com/en-us/library/ee617253.aspx

 

SET
ANSI_NULLS
ON

GO

SET
QUOTED_IDENTIFIER
ON

GO

— =============================================

— Author:        <Author,,Name>

— Create date: <Create Date,,>

— Description:    <Description,,>

— =============================================


 

Create
PROCEDURE CreateADAccount

    — Add the parameters for the stored procedure here

    @Firstname varchar(50),

    @Lastname varchar(50),

    @Email varchar(150)

AS

BEGIN

    — SET NOCOUNT ON added to prevent extra result sets from

    — interfering with SELECT statements.

    SET
NOCOUNT
ON;

 

    Declare @fullname varchar(300)

 

    set @fullname = @Firstname +
‘ ‘
+ @Lastname

 

    Declare @cmd varchar(3000)

 

    set @cmd =
‘powershell.exe New-ADUser -Name ”’
+ @fullname +
”’ -GivenName ”’
+ @Firstname +
”’ -Surname ”’
+ @Lastname +
”’ -Displayname ”’
+ @fullname +
”’ -SamAccountName ”’
+ @fullname +
”’ -Path ”CN=Users,DC=Inveniosoft,DC=com” -ChangePasswordAtLogon 1 -Enabled 1 -EmailAddress ”’
+ @Email +
”’ –AccountPassword (ConvertTo-SecureString ”ThisWillNeedToBeChanged#1” -AsPlainText –Force) ‘

 

    EXEC
master..xp_cmdshell
@cmd

 

END

GO

 

I created the first stored procedure using the fullname as the username, if you wanted it broken out you could the following procedure, note you’ll probably need to create some custom fields in the employee Info Center.

SET
ANSI_NULLS
ON

GO

SET
QUOTED_IDENTIFIER
ON

GO

— =============================================

— Author:        <Author,,Name>

— Create date: <Create Date,,>

— Description:    <Description,,>

— =============================================


 

Create
PROCEDURE CreateADAccount

    — Add the parameters for the stored procedure here

    @Firstname varchar(50),

    @Lastname varchar(50),

    @Displayname varchar(50),

    @Username varchar(50),

    @Email varchar(150)

AS

BEGIN

    — SET NOCOUNT ON added to prevent extra result sets from

    — interfering with SELECT statements.

    SET
NOCOUNT
ON;

 

    Declare @fullname varchar(300)

 

    set @fullname = @Firstname +
‘ ‘
+ @Lastname

 

    Declare @cmd varchar(3000)

 

    set @cmd =
‘powershell.exe New-ADUser -Name ”’
+ @fullname +
”’ -GivenName ”’
+ @Firstname +
”’ -Surname ”’
+ @Lastname +
”’ -Displayname ”’
+ @Displayname +
”’ -SamAccountName ”’
+ @Username +
”’ -Path ”CN=Users,DC=Inveniosoft,DC=com” -ChangePasswordAtLogon 1 -Enabled 1 -EmailAddress ”’
+ @Email +
”’ –AccountPassword (ConvertTo-SecureString ”ThisWillNeedToBeChanged#1” -AsPlainText –Force) ‘

 

    EXEC
master..xp_cmdshell
@cmd

 

END

GO

 

 

You can test the procedure before stored procedure by running the following:

exec CreateADAccount
‘tom’, ‘jones’, ‘Tom joness’, ‘tjone’, ‘tdsf@seco.com’

 

And you should get the following result:

Now all that’s left is to create the workflow in Vision. Start by going to the User Initiated Workflows and choose the Employee Info Center. The insert a new workflow for Insert/Associate, and insert a Stored Procedure for an Action.

Then enter the stored procedure name, and click the ellipses to choose the field from the Employee Info Center.

The first one in this case is First name.

Now to test it enter a new employee and save it.

And you should see the new Employee in AD.

So that’s the basics, there are certainly other workflows you can consider creating with this basis, and maybe I’ll document them later but things like disabling an account when an employee is set to inactive in Vision:

Disable-ADAccount -Identity "Username"

 

Another one which Kenny shared with me is creating exchange mailbox (which I don't have to test), which would look similar to this:
				

 

New-Mailbox -Name ‘[:EM.FirstName] [:EM.LastName]’ `

         -Alias ‘[:EmployeeCustomTabFields.CustAccountName]’ `

         -OrganizationalUnit ‘YOUR_DOMAIN.local/YOUR_OU_HERE/[:EmployeeCustomTabFields.CustOffice]’ `

         -UserPrincipalName ‘[:EmployeeCustomTabFields.CustAccountName]@YOUR_DOMAIN.local’ `

         -SamAccountName ‘[:EmployeeCustomTabFields.CustAccountName]’ `

         -FirstName ‘[:EM.FirstName]’ `

         -Initials ” `

         -LastName ‘[:EM.LastName]’ `

         -Password $password `

         -ResetPasswordOnNextLogon $false `

         -Database ‘YOUR_EXCHANGE_MAILBOX_DATABASE_HERE’ `

         -ActiveSyncMailboxPolicy ‘Default’

 

Or adding a new user to an AD Group (again your service account would need delegate access to that group), maybe by using a checkbox field in Vision:

Add-ADGroupMember ‘AutoCAD Users’ ‘Username’

 

If you have any comments to share please post them, or feel free to email me jj@projectinsight.io.

6 thoughts on “Creating Active Directory User Accounts from Vision Employees

  • June 2, 2015 at 7:11 pm
    Permalink

    Nice post. Really interesting stuff here. We do something similar with user account creation from Vision employees, but do it via custom windows service and SQL procs that run every 2 hours. With the same service we handle creation of AD accounts, assignment to AD Security and Distribution groups, exchange box setup, phone extension assignment, and lync account creation.

    Once you create the AD users automatically a whole new world opens up and you can do a ton. Good stuff.

    Reply
  • July 18, 2015 at 7:43 pm
    Permalink

    This is a great post that I ran across searching for a problem I’m running into. I notice Mike mentioned “assignment to AD Security and Distribution groups” and was wondering if anyone could supply a sample of that. We’re looking to assign users in AD to specific client groups based on the client section of the associations tab in the employee info center, but it doesn’t seem those values are passable as parameters to the stored procedure. Any ideas?

    Reply
    • July 20, 2015 at 1:42 pm
      Permalink

      You would want to use this function Add-ADGroupMember ‘Client Group’ ‘Username’ in the stored procedure. so your procedure would look something like this. You may need some additional logic to map your client names to the groups. Note I did not test this, you may need to tweek it.:

      Create
      PROCEDURE AddAccountToClientGroup

      — Add the parameters for the stored procedure here

      @Username varchar(50),

      @ClientGroupName varchar(150)

      AS

      BEGIN

      — SET NOCOUNT ON added to prevent extra result sets from

      — interfering with SELECT statements.

      SET
      NOCOUNT
      ON;

      Declare @cmd varchar(3000)

      set @cmd =
      ‘powershell.exe Add-ADGroupMember ”’
      + @Username +
      ”’ , ”’
      + @ClientGroupName

      EXEC
      master..xp_cmdshell
      @cmd

      END

      GO

      Reply
      • February 12, 2016 at 10:31 pm
        Permalink

        Thank you that was very helpful. We have had this functioning now for quite some time and have now decided to move our system to the Deltek Cloud service. As a result customizations to the database and access to our AD structure and UNC Shares will no longer be available. I’m thinking of converting my code logic to a web service and call that from workflow instead of the stored procedure and run it from an internal web server. Have any of you guys done those? I see the timesheet validator, but I’m pretty sure there is a lot more in that then is necessary as it pertains to parameters, etc.

        Reply
        • February 16, 2016 at 2:24 pm
          Permalink

          I’ve used web services before, but not to create AD accounts. It should work fine but you’ll want to think through what service accounts the site runs on and it would open a pretty big security hole, since I don’t think you can authenticate a call from the deltek cloud, or at least I’m not sure how you would. Maybe your could restrict the IP addresses that call into that web service.

          Reply
  • February 16, 2016 at 2:34 pm
    Permalink

    Thanks for the response. I actually have a big chunk of the folder creation routines completed as a web service. Plan on doing the AD group part now. I definitely agree about the security hole, and will probably implement some form of key exchange as one of the parameters being sent to the web service.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *