SQL Server support

Connector features

Data catalog

Dataedo documents metadata of following objects:

• Tables
• Views
• Procedures
• Functions
• Sequences
• Jobs
• Jobs steps
• Linked servers

For more details, go to Connector specification section

Data Lineage

For SQL Server Dataedo supports both manual and automatic lineage. Automatic lineage is currently available only for views. Dataedo will parse views and show source of each of the columns:

Data Profiling

You can profile data in tables and views to get a grasp of data sitting in your database:

Relationships (PK/FK) Tester

With Dataedo you can check if selected columns are viable candidates to create a relationship between tables:

Linked Servers

The SQL Server Connector imports linked servers into Linked Sources, capturing the connection details with the name assigned to each Linked Server. The import process does not automatically assign the linked source to objects, nor does it automatically associate the source database with the linked server. These assignments can be made manually by the user.

Important note

If a view is created using a Linked Servers connection object, the user must assign the linked server and parse the script to establish lineage.

How to connect

1. Server name - IP address or host name under which database is available
2. Port - port number under which database is available
3. Authentication - way of authentication to be used when connecting to SQL Server:
   • Windows Authentication - authentication that will use currently logged in Windows user. Does not require providing any other information.
   • SQL Server Authentication - SQL authentication. Requires SQL login:
     • User
     • Password
   • • Azure Active Directory - Password - authentication with Azure AD login. Requires:
       • User - AD username
       • Password - password for the provided username
   • Azure Active Directory - Integrated - this authentication will use AD account currently logged in Windows
   • Azure Active Directory - Universal with MFA - authentication through external Azure AD service. You can optionally provide username.
4. Connection mode - specifies if connection will be encrypted.
   • Encryped connection if possible - lest restrictive mode. If encryption is possible, data will be encrypted. Otherwise it will be send in plain text.
     • Force encryption, trust server certificate - Dataedo will not connect unless connection can be encrypted. SSL certificate on server will not be verified.
     • Force encryption, require trusted certificate - Most restrictive mode. Dataedo will connect only if connection can be encrypted and SSL certificate on server is valid.
5. Database - database name. For some types of authentication it is possible to list available databases by clicking three dot icon [...]. To import Jobs from SQL Server select < SQL Server Agent Objects >.

Connector specification

Supported versions

2008, 2008 R2, 2012, 2014, 2016, 2017, 2019, 2022.

Supported editions

LocalDB, Express, Developer, Standard, Business Intelligence and Enterprise.

Parallel Data Warehouse

Parallel Data Warehouse is not currently supported

Imported objects

Tables metadata

Views metadata

Procedures metadata

Functions metadata

Sequences metadata

Jobs metadata

Jobs steps metadata

Linked Servers

Descriptions & extended properties

Dataedo reads and writes extended properties from/to following SQL Server objects:

Data profiling

Datedo supports following data profiling in PostgreSQL:

Read more about profiling in a Data Profliling documentation.

Data Lineage

Column-level data lineage is retived using Dataedo SQL parser. Read more about capabilities of Transact-SQL SQL parser

See also: dbt, SSIS

Limitations

Following schema elements currently are not supported:

• Check constraints
• Non unique indexes
• Unique indexes on views (planned)
• Column level lineage for views with recursive CTE

Required access level

Importing database schema requires certain access level in documented database. Granting VIEW DEFINITION permission in the documented database to a new user will allow them to import all objects from the database. Alternatively, you can grant this permission only for specific objects you want to document.

Following objects are accessed during schema import process:

• INFORMATION_SCHEMA.COLUMNS
• INFORMATION_SCHEMA.KEY_COLUMN_USAGE
• INFORMATION_SCHEMA.PARAMETERS
• INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
• INFORMATION_SCHEMA.ROUTINES
• INFORMATION_SCHEMA.ROUTINE_COLUMNS
• INFORMATION_SCHEMA.TABLES
• INFORMATION_SCHEMA.VIEWS
• sys.all_objects
• sys.columns
• sys.computed_columns
• sys.extended_properties
• sys.foreign_key_columns
• sys.indexes
• sys.index_columns
• sys.objects
• sys.procedures
• sys.servers
• sys.sql_dependencies
• sys.sql_expression_dependencies
• sys.sql_modules
• sys.tables
• sys.views
• sysobjects
• sysusers
• sys.sequences
• sys.schemas
• sys.types

Importing jobs and jobs steps uses views from msdb System Database. Requires membership in the public role, due to usage of sp_executesql.

Following objects are accessed during jobs import process (user needs Select permission on those tables to import jobs) :

• dbo.sysjobs
• dbo.sysjobssteps
• dbo.syscategories - used for extended properties
• sys.server_principals - used for extended properties

Importing Linked Servers require additional access:

• master.sys.servers
• master.sys.linked_logins

Learn more

Connect to SQL Server