How to create SQL server data source using Windows Authentication

Issue Description:

Following is a procedure for creating a data source on sql server that uses Windows Authentication (a.k.a Integrated Security) to authenticate the user.

Resolution:

See attached file. Or the following:

PROCEDURE FOR CREATING A DATA SOURCE ON SQL SERVER THAT USES WINDOWS AUTHENTICATION (aka INTEGRATED SECURITY) TO AUTHENTICATE THE USER

1. Go to the download page for Microsoft SQL Server JDBC Driver 3.0:

   http://www.microsoft.com/en-us/download/details.aspx?id=21599

   (NOTE: Do *not* be tempted to download the 4.0 version of the driver, even though it is more recent -- it will not work! [as of JasperReports Server v3.7.x])

   (NOTE2: If you are on a later version of JasperReports Server such as v5.5.  The reverse may be true.  Investigation in progress.]

2. Download and run the self-extracting executable:

   1033\sqljdbc_3.0.1301.101_enu.exe
   

3. Open the extracted folder sqljdbc_3.0enuauth

4. Open *either* the x64 *or* the x86 subfolder, depending on whether your version of Windows is 64-bit or 32-bit.

5. Copy the file sqljdbc_auth.dll to the folder that your app server automatically searches for DLLs.

   For Tomcat, this is the {tomcat}bin folder.

6. Restart your app server

7. Run JasperReports Server

8. Add a data source.  This brings up the Set Data Source Type and Properties page.

9. Set the Type dropdown box to JDBC Data Source

10. Enter a Name, Resource ID, and Description for your data source

11. In the Driver field, enter the following string:

    com.microsoft.sqlserver.jdbc.SQLServerDriver
    

12. In the URL field, enter the following string:

    jdbc:sqlserver://{host};databaseName={database};SelectMethod=cursor;integratedSecurity=true
    

13. In the User Name field, enter any non-blank string you want, for example "none"

14. In the Password field, enter any non-blank string you want, for example "none"

15. Set the Time Zone and Save Location fields if you are not happy with their default values

16. Click the Test Connection button

    A yellow banner should briefly appear at the top of the page, saying "Connection passed"

17. Click the Submit button to save the data source

Ref. Case #00012669

sqlserver_3.txt