Dell Boomi: Connecting to MS Sql Server Database with Windows Authentication Using SQL Connector
There is no straight forward solution to use a Boomi database connector to connect to MSSQL server database using windows authentication.
Instead, it needs several windows configurations, atom settings and connector configurations.
1. Configure the database connection
You can still use the SQL Server connector but specifically configure to the use the windows authentication via the integratedSecurity=true in Additional Options setting.
If you omit this option, it will try to connect via SQL server authentication for which you need to configure username/password.
More from Microsoft docs,
Integrated security
Database authentication modes, Windows Authentication/ SQL Server Authentication
2. Run the atom as the required user
You have to run the atom as the user that you want to use the windows credentials for SQL server connectivity.
1. If you have logged in to the system as the user that you want to connect to the SQL Server database (using windows auth), you can directly run the atom server.
2. If you have logged in as a different user, you can do this by, pressing shift & right click on the atom -> Run as a different user
Note: There is a windows service for Atom service created on atom setup, configure the same user as the log-on user so that it will make sure to run as the same user above in restarts via Boomi platform.
Now if you try to test the connection, it will show an error as below,
Error Message: " This driver is not configured for integrated authentication"
Basically, this is due to the inability of the application to use the current windows login as an authentication mode on SQL connectivity.
3. Enabling windows authentication to SQL connector
Download Microsoft JDBC Driver for SQL Server and extract the content.
There you will find a sqljdbc_auth.dll from Microsoft JDBC Driver x.x for SQL Server\sqljdbc_x.x\enu\auth\x64
Copy that file to the System32 folder of Windows.
Now test the connection,
......
