How to make windows authentication sql server connection?

, ,
1

Windows authentication sql server connection? If I need to create connection using Windows Authentication instead off user authentication, where I should put sqljdbc_auth.ddl

Thanks.

2

Hello,

Could you please provide us the elaborate use case. Its not very clear what you want to achieve?

Sasanka

3

Hi Sasanka,

Thanks for your mail. I am trying to make JDBC connection with MS SQL Server 2008 using windows authentication, due to some security concern i can not pass userid and password for that database for connection purpose. I know how to make DB connection with SQL Server in java using windows auth. we need to just set integratedSecurity=true in connection strng and put sqljdbc_auth.dll in classpath. The same thing I want to achieve in webMethods Adapter also.
I will be very thankful if you can do some help me on this.

Thanks,
Navneet

4

Hi, Navneet.

We do this in our shop. I would give you a specific example, but our VPN is down. This is really not a webMethods issue, it is a JDBC issue. You basically need to install an extra file that comes with your SQL server JDBC driver in the correct directory, and specify integratedSecurity=true; in the connection properties.

Here is the MS documentation from [URL]Microsoft Learn: Build skills that open doors in your career

[INDENT]Connecting with Integrated Authentication On Windows

The JDBC driver supports the use of Type 2 integrated authentication on Windows operating systems through the integratedSecurity connection string property. To use integrated authentication, copy the sqljdbc_auth.dll file to a directory on the Windows system path on the computer where the JDBC driver is installed.

The sqljdbc_auth.dll files are installed in the following location:

[FONT=courier new]\sqljdbc_<language>\auth[/font]

For any operating system supported by the Microsoft JDBC Driver for SQL Server, see Using Kerberos Integrated Authentication to Connect to SQL Server for a description of a feature added in Microsoft JDBC Driver 4.0 for SQL Server that allows an application to connect to a database using integrated authentication with Type 4 Kerberos.

Note
If you are running a 32-bit Java Virtual Machine (JVM), use the sqljdbc_auth.dll file in the x86 folder, even if the operating system is the x64 version. If you are running a 64-bit JVM on a x64 processor, use the sqljdbc_auth.dll file in the x64 folder.
Alternatively you can set the java.libary.path system property to specify the directory of the sqljdbc_auth.dll. For example, if the JDBC driver is installed in the default directory, you can specify the location of the DLL by using the following virtual machine (VM) argument when the Java application is started:

-Djava.library.path=C:\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_\enu\auth\x86[/INDENT]

5

Hi,

Can you pls tell where do we have to place the sqljdbc_auth.dll in webMethods folder?

Thanks,
Priya

6

I dont think you need to have sqljdbc_auth.dll file, atleast on webMethods.
Do you have sqljdbc4.jar in IS/packages/WmJDBCAdapter/code/jars and DataSource Class set to ‘com.microsoft.sqlserver.jdbc.SQLServerDataSource’. Make sure the user with which webMethods is running has the right permissions.
Are you getting login failed error?

HTH,
Suresh

7

Hi Suresh,

Thanks for the quick reply. Yes, I am getting login error for the JDBC Adapter.
The Adpater is trying to login with the same user as the IS service starts as.
The data source class is set to : com.microsoft.jdbcx.sqlserver.SQLServerDataSource.

I dont have the sqljdbc4.jar placed in IS/packages/WmJDBCAdapter/code/jars.

Let me try and get back. Is there anything else I need to do ?

Can you pls elaborate on the permission factor for the User?

Thanks,
Priya

8

Hi Suresh,

I got sqljdbc4.jar in IS/packages/WmJDBCAdapter/code/jars and restarted the IS.

But the Adapter still says login error .

We are working on IS 9.5.

Can you pls suggest what do we need to check for user permissions?

Thanks,
Priya

9

Not sure if there is anything different in 9.X

Anyways you could try adding the sqljdbc4.jar to \IS\lib\jars\ and restarting the server.

On the user I wanted to make sure that the user that IS service starts has permission to access the database(atleast select).

With IS service user credentials you could create a jdbc connection, without integrated authentication, just to verify if the IS service user can access the database.

10

Hi Suresh,

Its working now. Many thanks for your help.

I got the sqljdbc4.jar but it was an older version . Then we got the newer one (from 2012) and placed it in lib folder.

We also placed sqljdbc_auth.dll in C:\Windows\System32 folder and tried to create adapter ocnnection by using the url in the connection property. It worked for us.

Thanks again!

Best Regards,
Priya

11

Glad its working. You are welcome.