Get Help

SqlServer@1

SqlServer@1 reads data from MS SQL Server databases. Nothing to install — but it is deprecated; use SqlServer@2.

Connects to

MS SQL Server, on premises or in Azure, with SQL Server or Windows authentication. A legacy provider has no technology of its own — a data source keeps the technology of its replacement, SqlServer@2.

Example

Name
DWH
Technology
MS SQL Server
Provider
SqlServer@1
Connection string
Data Source=localhost;
Initial Catalog=DWH;
Integrated Security=SSPI;

Data sources:
- Name: DWH
  Provider: SqlServer@1
  Technology: SqlServer
  Connection string: >
    Data Source=localhost;
    Initial Catalog=DWH;
    Integrated Security=SSPI;

Connection string

A standard System.Data.SqlClient connection string — Key=Value pairs separated by semicolons. The keys you will use most:

Key Meaning
Data Source (or Server) Server name or address; add ,port or \instance when needed
Initial Catalog (or Database) Database to connect to
Integrated Security SSPI or true for Windows authentication; leave out when you send a user name and password
User Id, Password SQL Server credentials
Encrypt, TrustServerCertificate Encrypt the connection; TrustServerCertificate=true accepts a certificate the client does not trust

The full list is in the System.Data.SqlClient documentation.

A password, or the whole string, can come from an environment variable — %NAME% is replaced with the value of NAME; see How every provider is used.

How to upgrade

SqlServer@2 is backward-compatible with this provider: change SqlServer@1 to SqlServer@2 in your data source definitions and the connection string keeps working. In some cases you might need to add ;TrustServerCertificate=true to it — the newer driver encrypts connections by default and refuses a certificate it does not trust. We are not aware of anything else needed.

Settings

None beyond Connection string.

Query

One T-SQL statement that returns a result set — a SELECT, or an EXEC of a stored procedure that returns one.

Prerequisites

None. The driver ships with CAT. Windows; on Linux it has no extra dependencies and is expected to work, but it has not been tested there.

Limitations

No multi-factor authentication and no service principals — System.Data.SqlClient does not support them. Use SqlServer@2 for any Microsoft Entra ID authentication.

Serving test and data source definitions

SqlServer@1 can also serve lists of definitions: any statement that returns the columns of a test, data source or query definition works. Ready-made table scripts are in Store definitions in a database.

← PowerBI@1
Properties →