Keyword not supported: 'statslog_on'

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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();

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply