Configuring Connections: Microsoft SQL Server with Windows Authentication (Versions: 6.3+)

Versions: 8.0+

 Note: This article describes the setup for SQL Server Windows Authentication for non-Azure SQL Server instances. For Azure-based SQL Server with Windows Authentication, use QuerySurge's Connection Extensibility option .

Configuring QuerySurge Connections: SQL Server with Windows Authentication

When you create a QuerySurge Connection, the Connection Wizard will guide you through the process. Different types of QuerySurge connections require different types of information.

For a SQL Server Connection with Windows Authentication (also called integrated authentication), you will need the following information (check with a DBA or other knowledgeable resource in your organization):

• Server Name or IP address of the SQL Server (e.g. sqlsvr1.myserver.com, or 192.168.0.255)
• Either the Port for your SQL Server database (1433 is the default port), or an Instance Name if using Dynamic Ports
• The Database name
• You will need to make sure that the proper Windows authentication dll is deployed on your driver boxes

Launch the Connection Wizard

1. Log into QuerySurge as an Admin user.
2. To configure a Connection, select Configuration > Connection in the Administrative View tree (at the left).
3. Click on the Add button at the bottom left of the page to launch the Connection Wizard. Click Next.
   
   Note: Check the Advanced Mode checkbox for access to advanced features.
   
   
   
   
4. Provide a name for your connection. Select SQL Server as the Data Source. Click Next.
   
   Note: An SQL Server JDBC driver is bundled with QuerySurge, so you can install this driver from the QuerySurge Installer.  If you choose to download the driver instead, please verify the correct version needed at https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver15#java-and-jdbc-specification-support.
   
   Note: Because this option requires you to deploy both a SQL Server JDBC driver and the proper integrated authentication .dll to each Agent, this choice is listed in the Vendor drivers. For details on deploying the driver and dll, see Setup for SQL Server Windows Authentication.
   
   
   
   
5. Once you have selected your Data Source, you will need to choose your authentication method. In this article, we will focus on using Windows Authentication. Using Windows Authentication, there are two options to choose from. In each case, the Wizard will provide a list of information that you need in order to proceed.
   
   Note: This document describes setup for SQL Server with Windows Authentication. For instructions on setting up SQL Server using Database Authentication, see the companion document.
   
   
   • Windows Authentication - If your SQL Server instance is set up with a static port (usually 1433), choose this option and you will be asked to provide the port number on the next page.
     
     
     
     
   • Windows Authentication with Dynamic Ports - If you are using named SQL Server instances with Dynamic Ports, choose this option and you will be asked to provide an instance name to connect to on the next page.
     
     
     
     
6. Once you have chosen your authentication method, click Next. You will now be asked to enter the information described on the previous page based on the method chosen.
   
   
   • If you chose Windows Authentication, you will need to enter the following:
     
     • Database Server Name and Port
     • Database Name
       
       Required fields for your Connection Type are marked by an *.
       
       
       
   • If you chose Windows Authentication with Dynamic Ports, you will need to enter the following:
     
     • Database Server Name
     • Instance Name
     • Database Name
       
       Required fields for your Connection Type are marked by an *.
       
       
       
7. Once you have entered your connection details, click Next, then click on Test Connection.
   
   Note: You must have an Agent running with the driver for this Connection deployed in order to test the Connection.
   
   
   
   
8. Save the Connection.
   
   
   
   
9. Congratulations! You’ve created a QuerySurge Connection. Make sure that you have deployed the driver for this Connection to all your QuerySurge Agents. (See Adding a JDBC Driver to your QuerySurge Agent for instructions.)