Discover a Microsoft SQL Server database

Discover a Microsoft SQL Server database

To discover a Microsoft SQL Server database with the Discovery Companion, please first prepare the source YAML file with the following:

  • The specifications to access the source system
  • The specifications for the output file

 

Here is an example:

  • A Microsoft SQL Server database named AdventureWorks2017
  • A local output folder for the Discovery JSON file is named outputs.
    The complete path is:
    ./outputs/mssql_discovery_output.json
source:
  type: mssql
  config:
    # Coordinates
    host_port: localhost:1433
    database: AdventureWorks2017
    schema_pattern: {'allow': ['Sales.*'], 'deny': [], 'ignoreCase': True}
    # Credentials
    username: training
    password: training
sink:
  type: file
  config:
    filename: ./outputs/mssql_discovery_output.json

The output folder in your source YAML file should exist.

You need to enable the TCP/IP protocol to discover your SQL Server database. 

You can download this YAML file example here or create a .yaml file with the previous example and then adapt it:

  • The host and port (localhost and 1433 in the example)
  • The database name (AdventureWorks2017 in the example)
  • The schema_pattern (Sales in the example)
  • The username (training in the example)
  • The password (training in the example)
  • The output path

Then, you can use it in the Discovery Companion.

 

If you want to connect to another SQL Server instance than the default one (which is by default accessible on port 1433 as shown in the example below), you need to:

  • Open the SQL Server Manager for the version of this other instance. For this, execute the following command:
    • sqlservermanager15.msc for SQL Server 2019
    • sqlservermanager14.msc for SQL Server 2017
  • Under SQL Server Network Configuration > Protocols for + instance name, double-click on TCP/IP and open the IP Addresses tab
  • At the end of the list, assign a port other than the default one (1433) for IPAll in TCP Dynamic Ports:
  • Under SQL Server Services, right-click the service SQL SERVER (instance name) and choose Restart

The host_port value in the YAML configuration file should look similar to this:

host_port: 127.0.0.1:1451 #Adapt the post value

You can download this YAML file example here.

 

All possible configurations are described on the DataHub official website.