Setup Integration Test Config

Setup integration test config

Set up local integration-test connection settings for SQL Tools Service by:

• Preparing an XML file with connection/auth info for required test instances.
• Running the Microsoft.SqlTools.ServiceLayer.TestEnvConfig project with that XML.
• Producing sqlConnectionSettings.json at the user profile root and storing SQL passwords in credential storage.

When to use

• User asks to set up integration test connection config.
• User needs sqlConnectionSettings.json for integration tests.
• User mentions SQLConnectionInstancesTemplate.xml or TestEnvConfig.

Ground truth in repo

This workflow is implemented and exercised in:

• test/Microsoft.SqlTools.ServiceLayer.TestEnvConfig/Program.cs
• test/Microsoft.SqlTools.ServiceLayer.Test.Common/TestConfigPersistenceHelper.cs
• .github/workflows/integration-tests.yml and azure-pipelines/integration-tests.yml

Key behavior:

• XML instances are read from the file passed to TestEnvConfig.
• Each <Instance> must include VersionKey and DataSource.
• UserId and Password are optional.
• If environment variable <VersionKey>_password exists, it overrides <Password> from XML.
• Output is written to user root as sqlConnectionSettings.json.
• Passwords are removed from JSON and stored via test credential storage.

Required interaction with user

Always ask this first:

• Do you already have credentials you want to use?
• Or do you want me to provide T-SQL to create a test login/user on your test server?

If they already have credentials:

• Ask for server host, auth type, username (if SQL auth), and whether they want one server for both sqlOnPrem and sqlAzure or separate servers.
• Ask if the user wants to enter a local-only test password via chat, or if it's a real password that needs to be entered by them manually in the XML.

If they want T-SQL:

• Provide SQL to create a login/user suitable for integration tests.
• Then continue with XML setup using that login.

T-SQL template

Use this when user asks for SQL to create credentials. Replace placeholders first.

USE [master];
GO

IF NOT EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = N'testAccount')
BEGIN
    CREATE LOGIN [testAccount] WITH PASSWORD = N'<StrongPasswordHere>', CHECK_POLICY = OFF;
END
GO

-- CI currently grants sysadmin for broad integration test coverage.
-- If your environment requires least privilege, scope permissions accordingly.
ALTER SERVER ROLE [sysadmin] ADD MEMBER [testAccount];
GO

XML template

Create a local file (for example test/Microsoft.SqlTools.ServiceLayer.TestEnvConfig/SQLConnectionInstances.local.xml):

<?xml version="1.0" encoding="utf-8"?>
<Instances>
  <Instance VersionKey="sqlOnPrem">
    <DataSource>localhost</DataSource>
    <UserId>testAccount</UserId>
    <Password></Password>
  </Instance>
  <Instance VersionKey="sqlAzure">
    <DataSource>localhost</DataSource>
    <UserId>testAccount</UserId>
    <Password></Password>
  </Instance>
</Instances>

Notes:

• Keep VersionKey values as sqlOnPrem and sqlAzure unless tests are explicitly changed.
• For integrated auth, omit UserId and Password.
• Prefer env vars (sqlOnPrem_password, sqlAzure_password) over plaintext passwords.

Generate the integration test settings

From repo root (all platforms):

dotnet run --project test/Microsoft.SqlTools.ServiceLayer.TestEnvConfig/Microsoft.SqlTools.ServiceLayer.TestEnvConfig.csproj -- test/Microsoft.SqlTools.ServiceLayer.TestEnvConfig/SQLConnectionInstances.local.xml

Optional password override via env vars (recommended):

PowerShell:

$env:sqlOnPrem_password = '<password>'
$env:sqlAzure_password = '<password>'

bash/zsh:

export sqlOnPrem_password='<password>'
export sqlAzure_password='<password>'

Verification

• Confirm command prints completion message.
• Confirm profile-root file exists:
• Windows: $env:USERPROFILE/sqlConnectionSettings.json
• Linux/macOS: $HOME/sqlConnectionSettings.json
• Confirm generated JSON has connection entries and no plaintext password fields.

Common pitfalls

• Missing VersionKey on an <Instance>.
• Using wrong env var names (must match <VersionKey>_password).
• Assuming password stays in JSON (it is intentionally removed and stored in credential storage).
• Forgetting to pass the XML path argument when running TestEnvConfig.

Expected assistant behavior

• Ask the credentials-vs-T-SQL question first.
• If credentials path is chosen, gather non-secret values and generate XML.
• If T-SQL path is chosen, provide SQL first, then proceed to XML.
• Run dotnet run ... TestEnvConfig.csproj -- <xml path>.
• Report resulting sqlConnectionSettings.json path and what was generated.