SQL Server Connection Object with Active Directory User

[Legacy User]

Does anybody have an example of how to create a connection object for SQL Server using an active directory user? I've filled in the basic information (database,server) and then selected the alternative login as the LOGIN object containing my active directory user that has access to the database. However I just keep getting login failed. I've also tried supplying trusted_connection = true in the connection string additional parameters, but that doesn't seem to work either.

Ultimately, I'm just trying to use this connection object so that I can create a SQL variable that connects out to an external application database to get a piece of information. In most other cases I just use a SQL job where I provide the database name and server name, then specify my login object for the AD user. This works without issue. The only problem I seem to be having is the authentication when using these pieces of information in a connection object.

[Karin_Wasinger_8156]

For MS SQL you have to add the following additional parameter in the Connection object:

IntegratedSecurity | Value: true

For example:

I have a MS-SQL Server (a named instance) and connect with my SQLDBSERVICE Agent (which is started with my Windows Login) successfully with the following settings in the connection object:

Server: <server>\<named instance>

DB: <DB>

Login Data: <Windows Login>

PW: ********

Additional parameters | Connection Properties:

Parameter: IntegratedSecurity | Value: true

[Karin_Wasinger_8156]

Please also have a look at our Guide:
https://docs.automic.com/documentation/WEBHELP/English/all/components/AE/11.2/All%20Guides/help.htm#ucacob.htm%3FTocPath%3DAdministration%2520Guide%7CInstallation%7CService%2520Pack%2520and%2520Hotfix%2520Installation%7CInstalling%2520the%2520Agents%7C_____2

[Roney.Uba]

I've done this with the jdbc connection used by the JWP and JCP, but on one system the ;integratedSecurity=true was not added to the connection string (ucsvr.ini) and on the other it was needed. (also the sqljdbc_auth.dll was placed on the AutomationEngin\bin).

I would say that one of the system is a test, where the security is not so high and the other is Prod, so there are maybe so additional policies.

[Marcel Friedmann]

Hi,

maybe a bit late but maybe this is helpful for some users.

SQL Agents are Java-based Agents. JDBC is used for connectivity to any database type.
Windows authentication for SQL Server (AD domain user) requires either Kerberos or NTLM authentication. 
The SQL Agent (v 12.2) only supports this when the agent itself is started as the connection user (Service Manager Dialog > Run As setting). Also in the agents ini-file the windows authentication must be 1 (default is 0).

An undocumented workaround is to add the following advanced properties in the connection object (on the right for connection properties extensions):
authenticationScheme = NTLM
domain = <your_ad_domain>
userName = <username_without_domain>
integratedSecurity = true
The password is taken from the login object assigned to the job.
Also the newest microsoft jdbc driver version is necessary.

Another workaround is to utilize the Generic JDBC datatype:
Connection String = jdbc:sqlserver://<db-name:port>;databasename=<db_name>;integratedSecurity=true;authenticationScheme=JavaKerberos;userName=<user@domain>;password=<password>
The agent needs to run as any authentication domain user for that.
Alternatively the authenticationScheme=NTLM should also work here (not tested.)

Hope this helps someone.

BR
Marcel