How do you connect to a SQL Server Database using Windows Authentication?

Yellowfin FAQ shared this question 5 months ago
Answered

How do you change the JDBC Connection URL for the SQL Server Driver? It doesn't natively handle Windows Authentication like the jTDS Driver, but I'm running into issues where I need to change Driver for the Yellowfin Configuration Database.

Comments (2)

photo
1

There may be a time when you need to change the Connection details of the Yellowfin Configuration Database in the ROOT/WEB-INF/web.xml file, to move from SQL Authentication to Windows Authentication. One example of where you might experience this is when changing from the jTDS Driver to the SQL Server Driver, which handle the Connection syntax differently.


  1. You will need to change the JDBC Driver from JTDS to MSSQL. In the web.xml file you will need to make the changes outlined below. You may also need to change the Database User, Database Password and Database Encryption if they are different to what you were using previously. You should change the encryption to false, and enter the password in plaintext.

    Change this:

    <init-param>
    <param-name>JDBCDriverClass</param-name>
    <param-value>net.sourceforge.jtds.jdbc.Driver</param-value>
    </init-param>

    To this:
    <init-param>
    <param-name>JDBCDriverClass</param-name>
    <param-value>com.microsoft.sqlserver.jdbc.SQLServerDriver</param-value>
    </init-param>

    And Change this:
    <init-param>
    <param-name>JDBCUrl</param-name>
    <param-value>jdbc:jtds:sqlserver://<host>:<port>/<database></param-value>
    </init-param>

    To this:

    <init-param>
    <param-name>JDBCUrl</param-name>
    <param-value>jdbc:sqlserver://<host>:<port>;databaseName=<database>;integratedSecurity=true;</param-value>
    </init-param>

  2. In order to use the MSSQL Driver with Windows Authentication, you will need to download the Driver from here, and then navigate to /enu/auth/x64/mssql-jdbc_auth-8.2.2.x64.dll file depending if you are using a x86 or x64 machine. You will need to rename this file to sqljdbc_auth.dll and move it to the Java PATH specified by Tomcat into the /bin folder.
  3. Restart the Yellowfin Service, making sure to start the service with the Windows Authenticated User specified in the web.xml, in order to Connection to the SQL Server Database


If you continue to have any issues, please raise a ticket with our Support Team containing a detailed description of your issue, including any relevant screenshots.


Kind regards,

Yellowfin Support Team

photo
1

There may also be a time when you need to connect to a Data Source not in your local Domain, using Windows Authentication, where the steps differ slightly to the above. You will need to again use the jTDS Driver to do this.


  1. Check to see which version of the jTDS Driver is installed on your environment by navigating to appserver/webapps/ROOT/WEB-INF/lib
  2. Navigate to the jTDS website and download the associated driver version files
  3. Extract the file, where you will need to copy the x64/SSO/ntmlauth.dll file to the Java/bin directory utilised by Tomcat
  4. Once you've set this up you can set up a Data Source Connection in Yellowfin, the easiest way by using a Generic JDBC Data Source where you can specify the JDBC Connection URL. You can also use the jTDS Driver, however, you may run into issues that will be resolved by using the Generic JDBC Data Source option.
  5. You will need to add the parameters Domain=<yourDomainName>; and useNTML=true; as well as your standard connection parameters to the string, where you should now have a Connection


If you continue to have any issues, please raise a ticket with our Support Team containing a detailed description of your issue, including any relevant screenshots.


Kind regards,

Yellowfin Support Team