JDBC conncetion string using WindowsAuth to SQLS2014 named instance

Question

How should I write my connection string in jdbc when I want to connect to a SQL server where windows authentication is needed and I am connecting to a named instance?

So I have my connection string like

jdbc:sqlserver://sqlserver4\Test;datebaseName=TestDB;user=g\John;password=hello123

I have also tried with sqlserver4;namedInstance=Testinstead of sqlserver4\Test

I have tried under

Control Panel -> Administrative Tools -> ODBC Data Source

Shift Right-Click -> Run as different user

login as user=g\John password=hello123 with this test I can connect successfuly, but in my jdbc connection string I get Login failed for user g\John.

Do I have to setup my connection string in a specific way when using Windows User Authentication and named instance in the DB?

Answer 1

According to: https://msdn.microsoft.com/en-us/library/bsz5788z.aspx

If your application runs on a Windows-based intranet, you might be able to use Windows integrated authentication for database access. Integrated security uses the current Windows identity established on the operating system thread to access the SQL Server database. You can then map the Windows identity to a SQL Server database and permissions.

To connect to SQL Server using Windows integrated authentication, you must identify the Windows identity under which your ASP.NET application is running. You must also be sure that the identity has been granted access to the SQL Server database. This topic includes a code example that displays the current Windows identity of the ASP.NET application.

So try maybe adding this:

integratedSecurity=true;

At the end of your connection string and removing username and password

Answer 2

In Production system, all the database servers/hosts will have application specific user ids like report_app_user, dev_app_user,dev_env_user,test_env_user,uat_env_user and the JDBC api will connect it promptly.

Please talk to ITsupport team/db admin to get a application specific user id.

Otherwise, you will not be able to use db user which is tied with windows authentication.

Answer 3

I have tried all that, but it seems that I have to implement some spring security and setup an LDAP server to be able to connect through an AD user, with MuleESB. But thanks anyway :).