0

I am trying to connect to a SQL 2017 instance via SSMS 18.2 with the 'Additional Connection Parameters'

StatsLog_On=Yes;StatsLogFile=C:\Temp\TestODBC.txt

The documentation at Using Connection String Keywords with SQL Server Native Client is less than perfect, but it strongly implies that it should work. I know from other sources that the keywords StatsLog_On & StatsLogFile need to be used together.

I get this error message when I try to connect

===================================

Cannot connect to sandbox.

===================================

Keyword not supported: 'statslog_on'. (System.Data)


Program Location:

at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)

at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)

at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)

at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)

at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)

at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)

at Microsoft.SqlServer.Management.UI.ConnectionDlg.SqlServerType.Microsoft.SqlServer.Management.UI.ConnectionDlg.IServerType.GetConnectionObject(UIConnectionInfo ci)

at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)

at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Note: "Sandbpx" is the name of the SQL instance

As a reality check using Server=sandbox;MultiSubnetFailover=True works fine

3
  • 1
    The stack trace suggests your are using the managed SqlClient API, not SQL Server Native Client.
    – Dan Guzman
    Dec 31, 2019 at 13:35
  • @DanGuzman the keyword Driver is also not supported (at least in my testing) so maybe this question should be what keywords can I use in the connection string of SSMS? I will look into it more and see what I find. Thanks for you help. Dec 31, 2019 at 14:03
  • This Powershell command works for me. SSMS is a non-starter for APIs other than SqlClient: (New-Object System.Data.Odbc.OdbcConnection("Driver={SQL Server Native Client 11.0};Server=YourServer;Trusted_Connection=Yes;MultiSubnetFailover=Yes")).Open()
    – Dan Guzman
    Dec 31, 2019 at 22:43

1 Answer 1

2

It looks like SSMS is using the .NET System.Data.SqlClient API. As far as I can tell, the statslog_on connection property is not supported using this API.

I ran a simple program* that iterates over the key value pairs allowed by that API and came up with these keys that are permitted in connection strings for SqlClient connections:

Data Source
Failover Partner
AttachDbFilename
Initial Catalog
Integrated Security
Persist Security Info
User ID
Password
Enlist
Pooling
Min Pool Size
Max Pool Size
PoolBlockingPeriod
Asynchronous Processing
Connection Reset
MultipleActiveResultSets
Replication
Connect Timeout
Encrypt
TrustServerCertificate
Load Balance Timeout
Network Library
Packet Size
Type System Version
Authentication
Application Name
Current Language
Workstation ID
User Instance
Context Connection
Transaction Binding
ApplicationIntent
MultiSubnetFailover
TransparentNetworkIPResolution
ConnectRetryCount
ConnectRetryInterval
Column Encryption Setting
Enclave Attestation Url

This list was produced specifically using the .NET Framework 4.7.2. The list could be different depending on the versions of things that are used by different versions of SSMS.

For instance, I have SSMS 18.4 installed. Attaching a debugger tells me that it's using the .NET Framework 4.8 version of System.Data.dll:

Screenshot of Visual Studio Modules window showing System.Data.dll at version 4.8

Based on that, I would expect any of these to be allowed in an SSMS connection string (give or take what might have changed between version 4.7 and 4.8 of the framework). Of course, some of them might fail at runtime, might not be compatible with one another, or might depend on the type of database you're connecting to.

Some allowed aliases for those specific values are allowed, and documented here: SqlConnection.ConnectionString Property - Remarks

*here is the C# program I used to produce those values:

var builder = new SqlConnectionStringBuilder();
foreach (var key in builder.Keys)
{
    Console.WriteLine(key);
}
Console.ReadLine();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.