Search code examples
sqlsql-serveractive-directorylinked-server

Update Active Directory mail user attribute from SQL server 2008

Hello Stackoverflow members

I am working in an educational institution and we need to push certain information that this maintained in the MSSQL2008 database for our student management application.

I would like to know if there is a way to use a database trigger to automatically update a user attribute field in Active directory. This would specifically allow us to automatically update email aliases within the directory system as the users update their records.

Is there a way of doing this without writing a complete middleware application rather than a db trigger firing a stored procedure to update Active Directory.

I have seen a few posts regarding getting AD data into SQL server but so far I haven't found any posts going the other way.

Regards

Gus

Solution

  • I am not sure about SQL commands for that but xp_cmdshell commands lets you executes a given windows command string/batch file as an operating-system command shell and returns any output as rows of text.

    So using xp_cmdshell you can run a batch file/ windows command through SQL trigger that can update a user attribute field in Active directory.

    Syntax for xp_cmdshell is :

    xp_cmdshell {'command_string'} [, no_output]

    Arguments

    'command_string'

    Is the command string to execute at the operating-system command shell. command_string is varchar(8000) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.

    no_output

    Is an optional parameter executing the given command_string, and does not return any output to the client.

    Return Code Values

    0 (success) or 1 (failure)

    Result Sets

    for example, executing this xp_cmdshell statement returns a directory listing of the current directory.

    xp_cmdshell 'dir *.exe'

    For details on xp_cmdshell refer :http://technet.microsoft.com/en-us/library/aa260689(v=sql.80).aspx

    Hope it helps you to find a way...