dotnet/sqlclient

Microsoft.Data.SqlClient provides database connectivity to SQL Server for .NET applications.

Microsoft SqlClient Data Provider for SQL Server

Microsoft.Data.SqlClient is a .NET data provider for Microsoft SQL Server and the Azure SQL family of databases. It grew from a union of the two System.Data.SqlClient components which live independently in .NET Framework and .NET Core. Going forward, support for new SQL Server and Azure SQL features will only be implemented in Microsoft.Data.SqlClient.

Supportability

The Microsoft.Data.SqlClient package supports the following environments:

• .NET Framework 4.6.2+
• .NET 6.0+

Download

The Microsoft.Data.SqlClient NuGet package is available on NuGet.org.

SNI Package References

When targeting .NET Framework on Windows, a package reference to Microsoft.Data.SqlClient.SNI loads native Microsoft.Data.SqlClient.SNI.<platform>.dll libraries into the client's build directories.

When targeting .NET on Windows, a package reference to Microsoft.Data.SqlClient.SNI.runtime loads arm64, x64 and x86 native Microsoft.Data.SqlClient.SNI.dll libraries into subdirectories in the client's build directory.

Helpful Links

Topic	Link to File
Coding Style	coding-style.md
Guidelines for building the driver	BUILDGUIDE.md
Guidelines for Contributors	CONTRIBUTING.md
Changelog for all driver releases	CHANGELOG.md
Support Policy	SUPPORT.md
Code of Conduct	CODE_OF_CONDUCT.md
Copyright Information	COPYRIGHT.md

Our Featured Contributors

Special thanks to everyone who has contributed to the project. We thank you for your continuous support in improving the SqlClient library!

• Wraith (@Wraith2)
• Erik Ejlskov Jensen (@ErikEJ)
• Simon Cropp (@SimonCropp)
• Stefán Jökull Sigurðarson (@stebet)
• Shay Rojansky (@roji)
• Stephen Toub (@stephentoub)
• Rasmus Melchior Jacobsen (@rmja)
• Phillip Haydon (@phillip-haydon)
• Robin Sue (@Suchiman)

Up-to-date list of contributors: Contributor Insights

Release Notes

All preview and stable driver release notes are available under release-notes.

Porting from System.Data.SqlClient

Refer to porting-cheat-sheet.md for a safe porting experience from System.Data.SqlClient to Microsoft.Data.SqlClient and share your experience with us by enhancing this guide for future developers.

Still have questions?

Check out our FAQ. Still not answered? Create an issue to ask a question.

Security

Microsoft takes the security of our software products and services seriously, which includes all source code repositories managed through our GitHub organizations, which include Microsoft, Azure, DotNet, AspNet, Xamarin, and our GitHub organizations.

If you believe you have found a security vulnerability in any Microsoft-owned repository that meets Microsoft's Microsoft's definition of a security vulnerability, please report it to us as described below.

Reporting Security Issues

Please do not report security vulnerabilities through public GitHub issues.

Instead, please report them to the Microsoft Security Response Center (MSRC) at https://msrc.microsoft.com/create-report.

If you prefer to submit without logging in, send email to [email protected]. If possible, encrypt your message with our PGP key; please download it from the Microsoft Security Response Center PGP Key page.

You should receive a response within 24 hours. If for some reason you do not, please follow up via email to ensure we received your original message. Additional information can be found at microsoft.com/msrc.

Please include the requested information listed below (as much as you can provide) to help us better understand the nature and scope of the possible issue:

• Type of issue (e.g. buffer overflow, SQL injection, cross-site scripting, etc.)
• Full paths of source file(s) related to the manifestation of the issue
• The location of the affected source code (tag/branch/commit or direct URL)
• Any special configuration required to reproduce the issue
• Step-by-step instructions to reproduce the issue
• Proof-of-concept or exploit code (if possible)
• Impact of the issue, including how an attacker might exploit the issue

This information will help us triage your report more quickly.

If you are reporting for a bug bounty, more complete reports can contribute to a higher bounty award. Please visit our Microsoft Bug Bounty Program page for more details about our active programs.

Preferred Languages

We prefer all communications to be in English.

Policy

Microsoft follows the principle of Coordinated Vulnerability Disclosure.

License

The Microsoft.Data.SqlClient Driver for SQL Server is licensed under the MIT license. See the LICENSE file for more details.

Trademarks

This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow Microsoft's Trademark & Brand Guidelines. Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are subject to those third-party's policies.

sqlclient's Issues

SqlClient implementation and API is not complete in Core

The following members are on types implemented in .NET Core. However these particular members are missing, although they are present on Desktop.

The problem with this situation is

• it defies user expectations that if a type X exists, code from other platforms that use X will work on .NET Core
• it cannot be fixed without servicing -- it is not possible to supply a standalone package with the rest of the type definition

No work is necessarily required here for 2.0. It's not part of NS2.0. I didn't look at usage data. I'm just flagging this so we're doing this consciously.

---

[benrr101] Updating in 2024, note that SqlClient is now Microsoft.Data.SqlClient

Missing APIs List (as of 04/03/2024)

• T:System.Data.SqlClient.OnChangeEventHandler - Added circa 04/2020
• T:System.Data.SqlClient.PoolBlockingPeriod - Added circa 06/2020
• T:System.Data.SqlClient.SqlAuthenticationMethod - Added circa 08/2019
• M:System.Data.SqlClient.SqlBulkCopyColumnMappingCollection.Clear - Added circa 10/2021
• M:System.Data.SqlClient.SqlBulkCopyColumnMappingCollection.RemoveAt(Int32) - Added circa 10/2021
• F:System.Data.SqlClient.SqlBulkCopyOptions.AllowEncryptedValueModifications - Added circa 04/2020
• ▶️ M:System.Data.SqlClient.SqlClientFactory.GetService(Type)
• M:System.Data.SqlClient.SqlClientFactory.CreateCommandBuilder - Added 08/2019
• M:System.Data.SqlClient.SqlClientFactory.CreateDataSourceEnumerator
  • Implemented via System.Data.Common.DbProviderFactory which returns null.
• ❓ M:System.Data.SqlClient.SqlClientFactory.CreatePermission(PermissionState)
  • Still in netfx package, but not in netfx documentation
• P:System.Data.SqlClient.SqlClientFactory.CanCreateDataSourceEnumerator
  • Implemented via System.Data.Common.DbProviderFactory which returns false.
• ❓ M:System.Data.SqlClient.SqlClientPermission.Add(String,String,KeyRestrictionBehavior)
  • Still in netfx package, but not in netfx documentation
• ❓ M:System.Data.SqlClient.SqlClientPermission.Copy
  • Still in netfx package, but not in netfx documentation
• ❓ M:System.Data.SqlClient.SqlClientPermissionAttribute.CreatePermission
  • Still in netfx package, but not in netfx documentation
• T:System.Data.SqlClient.SqlClientMetaDataCollectionNames - Added circa 09/2021
• T:System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider - Added circa 08/2019
• T:System.Data.SqlClient.SqlColumnEncryptionCngProvider - Added circa 08/2019
• T:System.Data.SqlClient.SqlColumnEncryptionCspProvider - Added circa 08/2019
• T:System.Data.SqlClient.SqlColumnEncryptionKeyStoreProvider - Added circa 04/2020
• P:System.Data.SqlClient.SqlCommand.ColumnEncryptionSetting - Added circa 08/2019
• P:System.Data.SqlClient.SqlCommand.Notification - Added circa 08/2019
• ▶️ P:System.Data.SqlClient.SqlCommand.NotificationAutoEnlist
• M:System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery - Added circa 01/2021
• M:System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery(AsyncCallback,Object) - Added circa 08/2019
• M:System.Data.SqlClient.SqlCommand.BeginExecuteReader - Added circa 08/2019
• M:System.Data.SqlClient.SqlCommand.BeginExecuteReader(AsyncCallback,Object) - Added circa 08/2019
• M:System.Data.SqlClient.SqlCommand.BeginExecuteReader(AsyncCallback,Object,CommandBehavior) - Added circa 03/2020
• M:System.Data.SqlClient.SqlCommand.BeginExecuteReader(CommandBehavior) - Added circa 01/2021
• M:System.Data.SqlClient.SqlCommand.BeginExecuteXmlReader - Added circa 08/2019
• M:System.Data.SqlClient.SqlCommand.BeginExecuteXmlReader(AsyncCallback,Object) - Added circa 08/2019
• M:System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult) - Added circa 03/2020
• M:System.Data.SqlClient.SqlCommand.EndExecuteReader(IAsyncResult) - Added circa 08/2019
• M:System.Data.SqlClient.SqlCommand.EndExecuteXmlReader(IAsyncResult) - Added circa 08/2019
• M:System.Data.SqlClient.SqlCommand.ResetCommandTimeout - Added circa 08/2019
• T:System.Data.SqlClient.SqlCommandBuilder - Added circa 10/2021
• T:System.Data.SqlClient.SqlCommandColumnEncryptionSetting - Added circa 04/2022
• M:System.Data.SqlClient.SqlConnection.#ctor(String,SqlCredential) - Added circa 08/2019
• P:System.Data.SqlClient.SqlSonnection.AccessToken - Added circa 08/2019
• P:System.Data.SqlClient.SqlConnection.ColumnEncryptionKeyCacheTtl - Added circa 08/2019
• P:System.Data.SqlClient.SqlConnection.ColumnEncryptionQueryMetadataCacheEnabled - Added circa 08/2019
• P:System.Data.SqlClient.SqlConnection.ColumnEncryptionTrustedMasterKeyPaths - Added circa 08/2019
• P:System.Data.SqlClient.SqlConnection.Credential - Added circa 08/2019
• M:System.Data.SqlClient.SqlConnection.ChangePassword(String,SqlCredential,SecureString) - Added circa 08/2019
• M:System.Data.SqlClient.SqlConnection.ChangePassword(String,String) - Added circa 08/2019
• ▶️ M:System.Data.SqlClient.SqlConnection.EnlistDistributedTransaction(System.EnterpriseServices.ITransaction)
  • EnterpriseServices isn't supported in netcore, would this ever be supported?
• M:System.Data.SqlClient.SqlConnection.GetSchema - Added circa 08/2019
• M:System.Data.SqlClient.SqlConnection.GetSchema(String) - Added circa 08/2019
• M:System.Data.SqlClient.SqlConnection.GetSchema(String,String[]) - Added circa 08/2019
• M:System.Data.SqlClient.SqlConnection.RegisterColumnEncryptionKeyStoreProviders(IDictionary{String,SqlColumnEncryptionKeyStoreProvider}) - Added circa 08/2019
• T:System.Data.SqlClient.SqlConnectionColumnEncryptionSetting - Added circa 04/2022
• P:System.Data.SqlClient.SqlConnectionStringBuilder.AsynchronousProcessing - Removed from netfx
• P:System.Data.SqlClient.SqlConnectionStringBuilder.Authentication - Added circa 10/2021
• P:System.Data.SqlClient.SqlConnectionStringBuilder.ColumnEncryptionSetting - Added circa 10/2021
• P:System.Data.SqlClient.SqlConnectionStringBuilder.ConnectionReset - Obsolete in netfx
• P:System.Data.SqlClient.SqlConnectionStringBuilder.ContextConnection - Obsolete in netfx
• P:System.Data.SqlClient.SqlConnectionStringBuilder.Enlist - Added circa 10/2021
• P:System.Data.SqlClient.SqlConnectionStringBuilder.IsFixedSize - Added circa 10/2021
• ▶️ P:System.Data.SqlClient.SqlConnectionStringBuilder.NetworkLibrary
• P:System.Data.SqlClient.SqlConnectionStringBuilder.PoolBlockingPeriod - Added circa 10/2021
• P:System.Data.SqlClient.SqlConnectionStringBuilder.TransactionBinding - Added circa 10/2021
• ▶️ P:System.Data.SqlClient.SqlConnectionStringBuilder.TransparentNetworkIPResolution
• T:System.Data.SqlClient.SqlCredential - Added circa 08/2019
• P:System.Data.SqlClient.SqlDataAdapter.UpdateBatchSize - Added circa 10/2021
• M:System.Data.SqlClient.SqlDataReader.Close - Added circa 08/2019
• M:System.Data.SqlClient.SqlDataReader.GetData(Int32)
  • Implemented via System.Data.Common base implementation, which always throws NotSupported. Netfx implementation also always throws NotSupported.
• M:System.Data.SqlClient.SqlDataReader.GetSchemaTable - Added circa 08/2019
• M:System.Data.SqlClient.SqlDataReader.IsCommandBehavior(CommandBehavior) - Added circa 08/2019
• ▶️ T:System.Data.SqlClient.SQLDebugging
• T:System.Data.SqlClient.SqlDependency - Added circa 10/2021
• T:System.Data.SqlClient.SqlNotificationEventArgs - Added circa 10/2021
• T:System.Data.SqlClient.SqlNotificationInfo - Added circa 04/2020
• T:System.Data.SqlClient.SqlNotificationType - Added circa 04/2020
• M:System.Data.SqlClient.SqlParameter.#ctor(String,SqlDbType,Int32,ParameterDirection,Boolean,Byte,Bute,String,DataRowVersion,Object) - Added circa 03/2021
• M:System.Data.SqlClient.SqlParameter.#ctor(String,SqlDbType,Int32,ParameterDirection,Byte,Byte,String,DataRowVersion,Boolean,Object,String,String,String) - Added circa 06/2022
• M:System.Data.SqlClient.SqlParameter.ForceColumnEncryption - Added circa 06/2022
• M:System.Data.SqlClient.SqlParameter.UdtTypeName - Added circa 03/2021
• M:System.Data.SqlClient.SqlParameterCollection.Add(String,Object) - Removed from netfx
• M:System.Data.SqlClient.SqlParameterCollection.Add(String,SqlDbType,Int32,String) - Added circa 03/2021

---

Old Missing APIs List

**Updated lists to reflect current status (5/15):**

Missing APIs

T:System.Data.SqlClient.OnChangeEventHandler
T:System.Data.SqlClient.PoolBlockingPeriod
T:System.Data.SqlClient.SqlAuthenticationMethod
M:System.Data.SqlClient.SqlBulkCopyColumnMappingCollection.Clear
M:System.Data.SqlClient.SqlBulkCopyColumnMappingCollection.RemoveAt(System.Int32)
F:System.Data.SqlClient.SqlBulkCopyOptions.AllowEncryptedValueModifications
M:System.Data.SqlClient.SqlClientFactory.System#IServiceProvider#GetService(System.Type)
T:System.Data.SqlClient.SqlClientMetaDataCollectionNames
T:System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider
T:System.Data.SqlClient.SqlColumnEncryptionCngProvider
T:System.Data.SqlClient.SqlColumnEncryptionCspProvider
T:System.Data.SqlClient.SqlColumnEncryptionKeyStoreProvider
P:System.Data.SqlClient.SqlCommand.ColumnEncryptionSetting
P:System.Data.SqlClient.SqlCommand.Notification
P:System.Data.SqlClient.SqlCommand.NotificationAutoEnlist
M:System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery
M:System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery(System.AsyncCallback,System.Object)
M:System.Data.SqlClient.SqlCommand.BeginExecuteReader
M:System.Data.SqlClient.SqlCommand.BeginExecuteReader(System.AsyncCallback,System.Object)
M:System.Data.SqlClient.SqlCommand.BeginExecuteReader(System.AsyncCallback,System.Object,System.Data.CommandBehavior)
M:System.Data.SqlClient.SqlCommand.BeginExecuteReader(System.Data.CommandBehavior)
M:System.Data.SqlClient.SqlCommand.BeginExecuteXmlReader
M:System.Data.SqlClient.SqlCommand.BeginExecuteXmlReader(System.AsyncCallback,System.Object)
M:System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(System.IAsyncResult)
M:System.Data.SqlClient.SqlCommand.EndExecuteReader(System.IAsyncResult)
M:System.Data.SqlClient.SqlCommand.EndExecuteXmlReader(System.IAsyncResult)
M:System.Data.SqlClient.SqlCommand.ResetCommandTimeout
T:System.Data.SqlClient.SqlCommandBuilder
T:System.Data.SqlClient.SqlCommandColumnEncryptionSetting
M:System.Data.SqlClient.SqlConnection.#ctor(System.String,System.Data.SqlClient.SqlCredential)
P:System.Data.SqlClient.SqlConnection.AccessToken
P:System.Data.SqlClient.SqlConnection.ColumnEncryptionKeyCacheTtl
P:System.Data.SqlClient.SqlConnection.ColumnEncryptionQueryMetadataCacheEnabled
P:System.Data.SqlClient.SqlConnection.ColumnEncryptionTrustedMasterKeyPaths
P:System.Data.SqlClient.SqlConnection.Credential
M:System.Data.SqlClient.SqlConnection.ChangePassword(System.String,System.Data.SqlClient.SqlCredential,System.Security.SecureString)
M:System.Data.SqlClient.SqlConnection.ChangePassword(System.String,System.String)
M:System.Data.SqlClient.SqlConnection.EnlistDistributedTransaction(System.EnterpriseServices.ITransaction)
M:System.Data.SqlClient.SqlConnection.RegisterColumnEncryptionKeyStoreProviders(System.Collections.Generic.IDictionary{System.String,System.Data.SqlClient.SqlColumnEncryptionKeyStoreProvider})
T:System.Data.SqlClient.SqlConnectionColumnEncryptionSetting
P:System.Data.SqlClient.SqlConnectionStringBuilder.AsynchronousProcessing
P:System.Data.SqlClient.SqlConnectionStringBuilder.Authentication
P:System.Data.SqlClient.SqlConnectionStringBuilder.ColumnEncryptionSetting
P:System.Data.SqlClient.SqlConnectionStringBuilder.ConnectionReset
P:System.Data.SqlClient.SqlConnectionStringBuilder.ContextConnection
P:System.Data.SqlClient.SqlConnectionStringBuilder.Enlist
P:System.Data.SqlClient.SqlConnectionStringBuilder.NetworkLibrary
P:System.Data.SqlClient.SqlConnectionStringBuilder.PoolBlockingPeriod
P:System.Data.SqlClient.SqlConnectionStringBuilder.TransactionBinding
P:System.Data.SqlClient.SqlConnectionStringBuilder.TransparentNetworkIPResolution
T:System.Data.SqlClient.SqlCredential
M:System.Data.SqlClient.SqlDataReader.IsCommandBehavior(System.Data.CommandBehavior)
M:System.Data.SqlClient.SqlDataReader.System#Data#IDataRecord#GetData(System.Int32)
T:System.Data.SqlClient.SQLDebugging
T:System.Data.SqlClient.SqlDependency
T:System.Data.SqlClient.SqlNotificationEventArgs
T:System.Data.SqlClient.SqlNotificationInfo
T:System.Data.SqlClient.SqlNotificationSource
T:System.Data.SqlClient.SqlNotificationType
M:System.Data.SqlClient.SqlParameter.#ctor(System.String,System.Data.SqlDbType,System.Int32,System.Data.ParameterDirection,System.Boolean,System.Byte,System.Byte,System.String,System.Data.DataRowVersion,System.Object)
M:System.Data.SqlClient.SqlParameter.#ctor(System.String,System.Data.SqlDbType,System.Int32,System.Data.ParameterDirection,System.Byte,System.Byte,System.String,System.Data.DataRowVersion,System.Boolean,System.Object,System.String,System.String,System.String)
P:System.Data.SqlClient.SqlParameter.ForceColumnEncryption
P:System.Data.SqlClient.SqlParameter.UdtTypeName
M:System.Data.SqlClient.SqlParameterCollection.Add(System.String,System.Object)
M:System.Data.SqlClient.SqlParameterCollection.Add(System.String,System.Data.SqlDbType,System.Int32,System.String)

By Design missing

• T:System.Data.SqlClient.SqlProviderServices - It is part of the EF provider model for old versions of EF that were part of .NET Framework. - see https://github.com/dotnet/corefx/issues/17126#issuecomment-301640792

Missing overrides

P:System.Data.SqlClient.SqlClientFactory.CanCreateDataSourceEnumerator
M:System.Data.SqlClient.SqlClientFactory.CreateCommandBuilder
M:System.Data.SqlClient.SqlClientFactory.CreateDataSourceEnumerator
M:System.Data.SqlClient.SqlClientFactory.CreatePermission(System.Security.Permissions.PermissionState)
M:System.Data.SqlClient.SqlClientPermission.Add(System.String,System.String,System.Data.KeyRestrictionBehavior)
M:System.Data.SqlClient.SqlClientPermission.Copy
M:System.Data.SqlClient.SqlClientPermissionAttribute.CreatePermission
M:System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider.DecryptColumnEncryptionKey(System.String,System.String,System.Byte[])
M:System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider.EncryptColumnEncryptionKey(System.String,System.String,System.Byte[])
M:System.Data.SqlClient.SqlColumnEncryptionCngProvider.DecryptColumnEncryptionKey(System.String,System.String,System.Byte[])
M:System.Data.SqlClient.SqlColumnEncryptionCngProvider.EncryptColumnEncryptionKey(System.String,System.String,System.Byte[])
M:System.Data.SqlClient.SqlColumnEncryptionCspProvider.DecryptColumnEncryptionKey(System.String,System.String,System.Byte[])
M:System.Data.SqlClient.SqlColumnEncryptionCspProvider.EncryptColumnEncryptionKey(System.String,System.String,System.Byte[])
P:System.Data.SqlClient.SqlCommandBuilder.CatalogLocation
P:System.Data.SqlClient.SqlCommandBuilder.CatalogSeparator
P:System.Data.SqlClient.SqlCommandBuilder.QuotePrefix
P:System.Data.SqlClient.SqlCommandBuilder.QuoteSuffix
P:System.Data.SqlClient.SqlCommandBuilder.SchemaSeparator
M:System.Data.SqlClient.SqlCommandBuilder.QuoteIdentifier(System.String)
M:System.Data.SqlClient.SqlCommandBuilder.UnquoteIdentifier(System.String)
M:System.Data.SqlClient.SqlConnection.GetSchema
M:System.Data.SqlClient.SqlConnection.GetSchema(System.String)
M:System.Data.SqlClient.SqlConnection.GetSchema(System.String,System.String[])
P:System.Data.SqlClient.SqlConnectionStringBuilder.IsFixedSize
P:System.Data.SqlClient.SqlDataAdapter.UpdateBatchSize
M:System.Data.SqlClient.SqlDataReader.Close
M:System.Data.SqlClient.SqlDataReader.GetSchemaTable

These were removed based on further investigation:

M:System.Data.SqlClient.SqlConnection.EnlistTransaction(System.Transactions.Transaction)
P:System.Data.SqlClient.SqlParameter.SourceVersion
P:System.Data.SqlClient.SqlParameterCollection.IsFixedSize
P:System.Data.SqlClient.SqlParameterCollection.IsReadOnly
P:System.Data.SqlClient.SqlParameterCollection.IsSynchronized

These were already fixed:

• M:System.Data.SqlClient.SqlClientFactory.CreateDataAdapter - dotnet/corefx@88f562c

---

Original obsolete list:

``` MembersMustExist : Member 'Microsoft.SqlServer.Server.SqlMetaData..ctor(System.String, System.Data.SqlDbType, System.Type)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'Microsoft.SqlServer.Server.SqlMetaData..ctor(System.String, System.Data.SqlDbType, System.Type, System.String)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'Microsoft.SqlServer.Server.SqlMetaData..ctor(System.String, System.Data.SqlDbType, System.Type, System.String, System.Boolean, System.Boolean, System.Data.SqlClient.SortOrder, System.Int32)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'Microsoft.SqlServer.Server.SqlMetaData.DbType.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'Microsoft.SqlServer.Server.SqlMetaData.Type.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServer(System.Data.DataRow[])' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServer(System.Data.DataTable)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServer(System.Data.DataTable, System.Data.DataRowState)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServer(System.Data.IDataReader)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServerAsync(System.Data.DataRow[])' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServerAsync(System.Data.DataRow[], System.Threading.CancellationToken)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServerAsync(System.Data.DataTable)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServerAsync(System.Data.DataTable, System.Data.DataRowState)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServerAsync(System.Data.DataTable, System.Data.DataRowState, System.Threading.CancellationToken)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServerAsync(System.Data.DataTable, System.Threading.CancellationToken)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServerAsync(System.Data.IDataReader)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopy.WriteToServerAsync(System.Data.IDataReader, System.Threading.CancellationToken)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlBulkCopyOptions System.Data.SqlClient.SqlBulkCopyOptions.AllowEncryptedValueModifications' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlClientFactory.CreatePermission(System.Security.Permissions.PermissionState)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand..ctor(System.String, System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlTransaction, System.Data.SqlClient.SqlCommandColumnEncryptionSetting)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery(System.AsyncCallback, System.Object)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.BeginExecuteReader()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.BeginExecuteReader(System.AsyncCallback, System.Object)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.BeginExecuteReader(System.AsyncCallback, System.Object, System.Data.CommandBehavior)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.BeginExecuteReader(System.Data.CommandBehavior)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.BeginExecuteXmlReader()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.BeginExecuteXmlReader(System.AsyncCallback, System.Object)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.Clone()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.ColumnEncryptionSetting.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(System.IAsyncResult)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.EndExecuteReader(System.IAsyncResult)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.EndExecuteXmlReader(System.IAsyncResult)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.Notification.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.Notification.set(System.Data.Sql.SqlNotificationRequest)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.NotificationAutoEnlist.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.NotificationAutoEnlist.set(System.Boolean)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlCommand.ResetCommandTimeout()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnection..ctor(System.String, System.Data.SqlClient.SqlCredential)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnection.AccessToken.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnection.AccessToken.set(System.String)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnection.ChangePassword(System.String, System.Data.SqlClient.SqlCredential, System.Security.SecureString)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnection.ChangePassword(System.String, System.String)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnection.ColumnEncryptionTrustedMasterKeyPaths.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnection.Credential.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnection.Credential.set(System.Data.SqlClient.SqlCredential)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnection.EnlistDistributedTransaction(System.EnterpriseServices.ITransaction)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnection.RegisterColumnEncryptionKeyStoreProviders(System.Collections.Generic.IDictionary)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.AsynchronousProcessing.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.AsynchronousProcessing.set(System.Boolean)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.Authentication.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.Authentication.set(System.Data.SqlClient.SqlAuthenticationMethod)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.ColumnEncryptionSetting.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.ColumnEncryptionSetting.set(System.Data.SqlClient.SqlConnectionColumnEncryptionSetting)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.ConnectionReset.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.ConnectionReset.set(System.Boolean)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.ContextConnection.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.ContextConnection.set(System.Boolean)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.Enlist.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.Enlist.set(System.Boolean)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.NetworkLibrary.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.NetworkLibrary.set(System.String)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.TransactionBinding.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.TransactionBinding.set(System.String)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.TransparentNetworkIPResolution.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlConnectionStringBuilder.TransparentNetworkIPResolution.set(System.Boolean)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlDataReader.IsCommandBehavior(System.Data.CommandBehavior)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlParameter..ctor(System.String, System.Data.SqlDbType, System.Int32, System.Data.ParameterDirection, System.Boolean, System.Byte, System.Byte, System.String, System.Data.DataRowVersion, System.Object)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlParameter..ctor(System.String, System.Data.SqlDbType, System.Int32, System.Data.ParameterDirection, System.Byte, System.Byte, System.String, System.Data.DataRowVersion, System.Boolean, System.Object, System.String, System.String, System.String)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlParameter.ForceColumnEncryption.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlParameter.ForceColumnEncryption.set(System.Boolean)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlParameter.UdtTypeName.get()' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlParameter.UdtTypeName.set(System.String)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlParameterCollection.Add(System.String, System.Data.SqlDbType, System.Int32, System.String)' does not exist in the implementation but it does exist in the contract. MembersMustExist : Member 'System.Data.SqlClient.SqlParameterCollection.Add(System.String, System.Object)' does not exist in the implementation but it does exist in the contract. ```

from https://github.com/dotnet/corefx/blob/master/src/shims/ApiCompatBaseline.netcoreapp.netfx461.txt

Suspicious comment about non-existing lock in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() has this warning:

  // call OnError outside of _ErrorCollectionLock to avoid deadlock

there's nothing called _ErrorCollectionLock or similarly in the codebase. The comment is full of magic.

SqlConnection GetSchema("DataTypes") method returns wrong datatype for tinyint

SqlConnection's method GetSchema("DataTypes") returns following results for "tinyint" data type

• DataType: System.SByte
• IsUnsigned:True

This result is inconsistent and it seems it should return System.Byte as DataType,
because SQLServer's tinyint data type is defined to be mapped to .NET byte type (0-254)
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings

Example code to reproduce problems

var conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=TestDB;Integrated Security=True;");
conn.Open();
var rows = conn.GetSchema(DbMetaDataCollectionNames.DataTypes)
                    .Rows
                    .OfType<DataRow>()
                    .Where(p=>(string)p["TypeName"]== "tinyint");
foreach (var row in rows)
{
	Console.WriteLine(String.Format("TypeName:{0,-25} DataType:{1,-30} IsUnsigned:{2,-15}",
	  row["TypeName"],
	  row["DataType"],
	  row["IsUnsigned"]
	));
}
//Output:
//TypeName:tinyint                   DataType:System.SByte                   IsUnsigned:True        

Root cause of problems

It seems SqlConnection GetSchema("DataTypes") method return results from following xml definition file.

https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/Resources/System.Data.SqlClient.SqlMetaData.xml#L804

Reproducible environments

I've confirmed this problem occurs following environments

• .NET 4.6.1
• .NET Core 2.0 with System.Data.SqlClient/4.4.0-preview2-25405-01

Note

It seems following issue mentioned this wrong tinyint datatype.
fsprojects/SQLProvider#69

SqlClient: Keyword not supported: 'TransparentNetworkIPResolution'

From @msmolka on March 10, 2017 12:50

I'm sharing connection string between net462 and core application.
connection string contains `TransparentNetworkIPResolution=False;'

When starting ASP NET Core app I have following error:

Exception message:  An exception occurred in the database while saving changes.
Stack trace:
Microsoft.EntityFrameworkCore.DbContext[1]
      An exception occurred in the database while saving changes.
      System.ArgumentException: Keyword not supported: 'transparentnetworkipresolution'.
         at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 pa

I'm expecting TransparentNetworkIPResolution is proper keyword.

Further technical details

EF Core version: 1.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows Server 2012
IDE: Visual Studio 2015

Copied from original issue: dotnet/efcore#7836

SqlClient's connection resiliency adds 10 seconds to database existence checks. Consider exposing a way to override it at runtime

This is to follow up on a specific issue a customer created today on EF Core at dotnet/efcore#7283, but the problem has been brought to our attention before.

Both EF6 and EF Core provide APIs that allow applications to create and initialize databases, check for their existence and modify their schemas. These operations can happen at design time (e.g. when using the migrations feature) or at runtime. Attempting to connect to a database that doesn't exist is part of the normal flow of these operations.

In .NET Framework 4.5.1 SqlClient introduced a connection resiliency feature that performs automatic retries if failures occur during SqlConnection.Open(). Two new settings ConnectRetryCount and ConnectRetryInterval were added to the list of settings recognized in connection strings and a default behavior was adopted so if the first attempt to connect to a database fails a retry will occur after 10 seconds if these settings are not specified. The same feature is now included in the .NET Core version of SqlClient.

With only the default behavior this feature introduces a lag of 10 second for any calling code that attempts to connect to a database that doesn't exist. It also prevents any calling code from implementing its own (potentially more efficient) retry logic correctly. This severely affects customer experience and can potentially affect runtime performance.

Two main approaches have been proposed to mitigate this issue on EF code, but they have severe disadvantages:

1. Recommend customers to disable SqlClient's connection resiliency altogether by setting ConnectRetryCount=0 when working EF and make sure the feature is disabled any time EF runtime or tooling creates a connection string, or even throw if an attempt is made to use a connection that has the feature enabled. The main disadvantages of this approach are:

• It is not discoverable for customers and so users will still experience the 10 second lags (or exceptions if we decide to throw) for the default case.
• It prevents the connection resiliency feature in SqlClient from being available in scenarios in which it could actually have been helpful.
• EF6 supports older versions of .NET Framework than 4.5.1 and for those versions these settings are not valid. The logic would need to take the executing version of .NET Framework into account.

2. Store aside a copy of the original connection string so that we can modify it with ConnectRetryCount = 0 and create a separate SqlConnection object to perform existence checks.

• The main problem with this approach is that both EF Core and EF6 support passing a SqlConnection object to be used in the EF context. It is possible that the password would have already been removed from the ConnectionString in that connection object if it was open before, so in that case the connection string would not contain enough credentials to be able to create a separate functional connection object.

At this point we believe that if SqlConnection exposed a way to programmatically disable connection retries without requiring the modification of the connection string we could modify EF6 and EF Core code to restore the correct behavior and eliminate 10 second lags. We are happy to discuss other options with the SqlClient team.

Also note that this issue applies to both .NET Core and .NET Framework.

cc @ajcvickers

Note on how EF Core implements its own retry logic to check for database existence:

In general, any code that is calling SqlConnection.Open() can leverage contextual knowledge to make connection retries more efficient. E.g.:

• When EF Core performs existence checks, it will only retry on certain errors if they occur immediately after a database has been created.
• For a regular existence checks (i.e. those that don't happen immediately after the database has been created) EF Core assumes that immediate failures coming from a database server mean that the database effectively doesn't exist and can avoid any retry logic.

CommandCancelTest.TimeoutCancel fails when using managed SNI with Encrypt=true

Test results:
FAILURE: - unexpected non-open state after Execute!
Expected: True
Actual: False

Stack Trace:
System.Data.SqlClient\tests\ManualTests\SQL\CommandCancelTest\CommandCancelTest.cs(198,0): at System.Data.SqlClient.ManualTesting.Tests.CommandCancelTest.VerifyConnection(SqlCommand cmd)
System.Data.SqlClient\tests\ManualTests\SQL\CommandCancelTest\CommandCancelTest.cs(154,0): at System.Data.SqlClient.ManualTesting.Tests.CommandCancelTest.TimeoutCancel(String constr)

CC @saurabh500

SqlClient high memory consumption when there is disk IO saturation

Repro environment

• Windows 10
• LocalDb
• npm
• loadtest: npm install -g loadtest
• dotnet 1.0.0-preview2-002823

Scenario description

Stress test for ASP.NET and Entity Framework. The code is here. There's a server side and a client side to the test.
For the server side, execute the app with dotnet run -c Release.
For the client side, execute .\loadtest.ps1 -iterations 50000 -rps 500
The -rps 500 part is important, as the issue only manifests itself when disk IO is saturated.

Observed results

Once the test starts running we're reaching disk IO saturation almost immediately. The memory increases dramatically, up to 1 GB in private bytes when loadtest is run with the parameters stated above. After the test is up, we force GC collection once and take some measurements of the managed heap:

• Gen 0: 49,440 bytes
• Gen 1: 384,344 bytes
• Gen 2: 5,178,176 bytes
• LOH: 6,375,520 bytes

Totalling roughly 12 MB.

That said, the application is currently taking 959 MB in total committed memory. I'm assuming then that there's native memory being held.

A profile trace of the execution shows that most of the time is being spent in ReadSniSyncOverAsync.

Expected results

We expect the memory to not grow as much as it's doing now.

Notes

After forcing GC collection a number of times, we end up bringing the committed memory down to 515 MB and it stabilizes there. The managed heap shrinks from 12 MB to 10 MB.

Cancelling an async SqlClient operation throws SqlException, not TaskCanceledException

When using a cancellation token to cancel an SqlClient async operation, an SqlException is thrown rather than TaskCanceledException. This seems like problematic behavior, here's some analysis.

On the problematic side, standard async APIs generally throw TaskCanceledException, and SqlClient is doing something different and therefore unexpected. Also, as a result, canceled SqlClient tasks don't have status Canceled, they're Faulted instead. This makes differentiating cancellations from real exceptions more difficult, and the problem is compounded if the SqlClient call is just part of a larger async operation (which again, will be faulted instead of canceled unless some manual specific handling is involved).

The argument for the current behavior seems to be that all of SqlClient's server and network errors are raised as SqlException, making it easy to catch all database errors under a single exception type. On the other hand, it seems that cancellation isn't really an error condition as it's been requested by the user and is expected to occur. The TaskCanceledException is simply a mechanism for conveying the cancellation up the stack.

For the record, this was originally reported by @mikkeljohnsen for Npgsql in npgsql/npgsql#1146. Whatever is decided here for SqlClient will also be adopted for Npgsql (which currently behaves like SqlClient in this respect).

Column Encryption (Always Encrypted) support in SqlClient for .NET Core

When using a DbContext with a ConnectionString like

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.\\SQLEXPRESS;Database=EFGetStarted.AspNetCore.NewDb;Trusted_Connection=True;Column Encryption Setting=Enabled;MultipleActiveResultSets=true;"
  }
}

I get the following error
StackTrace

{System.ArgumentException: Keyword not supported: 'column encryption setting'.
   at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms)
   at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
   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.EntityFrameworkCore.Storage.Internal.SqlServerConnection.CreateDbConnection()
   at Microsoft.EntityFrameworkCore.Internal.LazyRef`1.get_Value()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(Tuple`2 parameters)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at xBN.Repository.Repositories.EntityBaseRepository`1.Commit() in C:\!Checkouts\git checkouts\xBN\xBN.Service\Repositories\EntityBaseRepository.cs:line 98
   at xBN.API.Controllers.CompanyController.Get(Int32 id) in C:\!Checkouts\git checkouts\xBN\xBN.API\Controllers\CompanyController.cs:line 37
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__27.MoveNext()}

The issue is about adding support for the Column Encryption Setting to the .NET Core ADO.NET SqlClient.

System.Data.SqlClient | UWP ARM Build

Win 10 IoT ARM Build

The .NET Core Foundation Libraries already got Builds for most common platforms and architectures.

Since the release of Windows 10 IoT Preview Version and announced support for Raspberry Pi Products many developers want to use it in a commercial way but the current build does not inherit the full core. The main issue with the current IoT-Build is the missing ability to connect to MS SQL Server and pass your requests. Using an additional WCF Service and communicate over REST-API is not the proper way to transfer Data in a company.

So my request is to add a build of the .NET CORE on ARM for Windows 10 IoT usage on the github list or at least adding the ability to handle SQL for MS SQL Server.

tl;dr
No possibility to connect to MS SQL Server via UWP
Should be implemented since Raspberry Pi 3 got Win 10 IoT and is rising interest for companies

Please consider:
According to the porting documentation and a missing branch for IoT ARM I am leaving this request/issue on the master page.
There is no other project handling my request

System.Data.SqlClient.SqlConnection.PermissionDemand() method name doesn't match what it does

There's System.Data.SqlClient.SqlConnection.PermissionDemand() method which actually checks whether the connection strings are proper set. That doesn't sound like "permission demand" at all.

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() silently swallows error conditions

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() has a Debug.Assert check that the errors connection is not empty and also this excellent if:

 SqlConnection exception = null;
 //later...
 if (temp != null && temp.Count > 0)
      //craft exception
 }

 //later ...
 if (exception != null ) {
     //do a lot of cool stuff
 } // else do nothing

which means that allowing to have this function called when there're no elements in temp leads to a logical error - no error indication is given and the connection is just silently closed.

Those Debug.Asserts should instead throw something like InvalidOperationException.

Failing Debug Assert in TdsParserStateObject.ReadAsyncCallback

Fails in SqlClient manual tests when running with Managed SNI in debug mode.

https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/TdsParserStateObject.cs#L2686

SqlTransaction and TransactionScope leak isolation level

The TransactionScope class seems to "leak" it's isolation level to future queries on the same (pooled) connection.

I would expect the isolation level of the connection to be restored when the transaction ends or is disposed.

Here is a snippet which reproduces the behavior:

SqlConnection.ClearAllPools();
var conn = new SqlConnection(new SqlConnectionStringBuilder { DataSource = @".\sqlexpress", IntegratedSecurity = true }.ConnectionString);

Action printIsolationLevel = () =>
{
	var cmd = conn.CreateCommand();
	cmd.CommandText = @"SELECT CASE transaction_isolation_level 
						WHEN 0 THEN 'Unspecified' 
						WHEN 1 THEN 'ReadUncommitted' 
						WHEN 2 THEN 'ReadCommitted' 
						WHEN 3 THEN 'Repeatable' 
						WHEN 4 THEN 'Serializable' 
						WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
						FROM sys.dm_exec_sessions 
						where session_id = @@SPID";
	Console.WriteLine(cmd.ExecuteScalar());
};

conn.Open();
printIsolationLevel(); // "ReadCommitted"
conn.Close();

using (var scope = new TransactionScope(
					TransactionScopeOption.RequiresNew,
					new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.Serializable }))
{
	conn.Open();
	printIsolationLevel(); // "Serializable"
	conn.Close();
}

conn.Open();
printIsolationLevel(); // "Serializable" !!?
conn.Close();

SqlConnection.CreateCommand() - Auto-Enlist in Pending Local Transaction

When CreateCommand() is called on a SqlConnection that is in a transaction, the SqlCommand returned by that method is not associated with the connection's transaction. Instead, the command must be explicitly informed of the transaction or an exception similar to the following will be thrown when the command is executed:

MethodInvocationException: Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."

Cost of the Manual Wire-Up

• The need to manually associate the command with the connection's transaction adds a small amount of code clutter.

    using (var connection = new SqlConnection(connectionString)) {
        connection.Open();
        var transaction = connection.BeginTransaction();
  
        using (var command = connection.CreateCommand()) {
            command.Transaction = transaction; // code clutter
    }
  

• More significantly, the need to explicitly set up this association when a transaction is used means that modifying a high-level component to use transactions requires code changes to lower-level components sharing the same SqlConnection, even when these lower-level components are transaction-agnostic.

    class Service
    {
        public void Process()
        {
            /*
              Modifying Process() to use transactions isn't as simple as uncommenting the below 
              because component also needs to be modified to wire up its SqlCommand to use the 
              transaction even though component may not care whether it is in a transaction. 
           */
            
            // using (var transaction = connection.BeginTransaction())
            // {
                component.Process(connection);
  
            //    transaction.Commit();
            // }
        }
    }
  

• All methods executing SqlCommands on the connection when the transaction is in use must have access to the transaction object. SqlConnection does not provide a public property exposing its transaction, so the object either must be passed around or accessed via reflection.

    using (var command = connection.CreateCommand()) {
        command.Transaction = connection.Transaction; // not possible because connection does not publically expose the transaction
    }
  

Proposal

When CreateCommand() is called on a SqlConnection in a transaction, the SqlCommand it returns should be associated with the connection's transaction, eliminating the need for the explicit wire-up.

[Added: 2019-06-03]:
Alternately (perhaps better yet), change command execution so that it automatically uses the transaction associated with the connection when it's executed. With this approach, any command (whether or not created by CreateCommand()) will execute against the proper transaction. Existing logic requires that the connection's transaction is used; the change proposed here is for the command to automatically use this transaction vs. the current behavior of the command throwing an exception when the transaction hasn't been explicitly referenced.

System.Data.SqlClient.SqlInternalConnectionTds constructor purges original call stack when rethrowing an exception

System.Data.SqlClient.SqlInternalConnectionTds currently goes like this:

try
{
    OpenLoginEnlist(timeout, connectionOptions, redirectedUserInstance);
    break;
}
catch (SqlException sqlex)
{
    if (something)
    {
         throw sqlex;
    }

throw sqlex causes original call stack of where the exception was thrown to be lost. If that's what it desired then it should be explained, otherwise just use throw;

System.Data.SqlClient.SqlConnection does not roll back open transaction on dispose

SQL Server version is 2014 if it matters. Trivial reproduction causes timeout unless ;pooling=false is appended to the connection string.

    using System;
    using System.Data;
    using System.Data.SqlClient;

    /*
    CREATE TABLE Scratch (ID INTEGER NOT NULL);
    INSERT INTO Scratch VALUES (1);
    */

    namespace ConsoleApplication
    {
        public class Program
        {
            public static void Main(string[] args)
            {
                if (args.Length == 0) {
                    Console.WriteLine("Usage: dotnet disposeconnection.dll connectionstring");
                    return ;
                }
                using (var c1 = new SqlConnection(args[0]))
                {
                    c1.Open();
                    using (var c2 = new SqlConnection(args[0]))
                    {
                        c2.Open();
                        Exec(c2, "BEGIN TRANSACTION;");
                        Exec(c2, "UPDATE Scratch SET ID = 2;");
                    }
                    Exec(c1, "UPDATE Scratch SET ID=3;");
                }
            }

            private static void Exec(SqlConnection c, string s)
            {
                using (var m = c.CreateCommand())
                {
                    m.CommandText = s;
                    m.ExecuteNonQuery();
                }
            }
        }
    }

We got here through a much larger chunk of code handling thrown exceptions. We have no idea what the connection state is and it might have an executing data reader on it so we have no good way of passing the rollback command ourselves.

SqlClient: Support SqlAuthenticationProvider and authentication connection string keyword for AAD auth

AAD authentication, including MFA, is already supported on SqlClient for .NET Core from version 2.2, by first obtaining the AccessToken using the ADAL library or some other method against Azure AD, and then setting it on the AccessToken property of the SqlConnection.

This issue is about enabling a different pattern for AAD authentication that is supported in .NET Framework, in which SqlClient takes care of obtaining the AccesToken automatically based on an authentication keyword in the connection string and SqlAuthenticationProvider implemetnations.

Original issue text

Please add AAD auth support for Azure SQL Database the way how that' s supported in .NET framework 4.6. We should be able to use Authentication modes like "Active Directory Integrated" and "Active Directory Password" in the connecting string.
Currently, including aforementioned authentication modes result into following error:
“Keyword not supported: ‘authentication’.

DAC connection fails sometimes, possibly due to unreleased resource after connection closed.

DAC (Dedicated Admin Connection) allows only 1 connection, and it will throw exception if another connection attempts to be established when current connection is not closed and released.
In Managed SNI, sometimes and not always, when a DAC connection opened and then closed, and another DAC connection try to be made, it fails. It seems some resource is not released right away after connection closed.
It throws exception with following stack trace.

System.Data.SqlClient.SqlException : A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)
---- System.IO.IOException : Unable to read data from the transport connection: An established connection was aborted by the software in your host machine.
-------- System.Net.Sockets.SocketException : An established connection was aborted by the software in your host machine
Stack Trace:
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\SqlInternalConnection.cs(250,0): at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapClos
eInAction)
D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\TdsParser.cs(1111,0): at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean
asyncClose)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\TdsParserStateObject.cs(932,0): at System.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\TdsParserStateObject.cs(2572,0): at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\TdsParserStateObject.cs(2107,0): at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\TdsParserStateObject.cs(2032,0): at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()

D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\TdsParser.cs(666,0): at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable)

D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\TdsParser.cs(375,0): at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\SqlInternalConnectionTds.cs(1403,0): at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\SqlInternalConnectionTds.cs(1160,0): at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, TimeoutTimer timeout)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\SqlInternalConnectionTds.cs(1014,0): at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, Boolean redirectedUserInstance)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\SqlInternalConnectionTds.cs(372,0): at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\SqlConnectionFactory.cs(133,0): at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\ProviderBase\DbConnectionFactory.cs(114,0): at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\ProviderBase\DbConnectionPool.cs(476,0): at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\ProviderBase\DbConnectionPool.cs(1187,0): at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\ProviderBase\DbConnectionPool.cs(792,0): at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\ProviderBase\DbConnectionPool.cs(712,0): at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\ProviderBase\DbConnectionFactory.cs(281,0): at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\ProviderBase\DbConnectionInternal.cs(452,0): at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\ProviderBase\DbConnectionClosed.cs(64,0): at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\SqlConnection.cs(1003,0): at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\SqlConnection.cs(596,0): at System.Data.SqlClient.SqlConnection.Open()
D:\Projects\CoreFx\src\System.Data.SqlClient\tests\FunctionalTests\SqlConnectionTest.cs(85,0): at System.Data.SqlClient.Tests.SqlConnectionBasicTests.IntegratedAuthTest()
   ----- Inner Stack Trace -----
D:\Projects\CoreFx\src\System.Net.Sockets\src\System\Net\Sockets\NetworkStream.cs(301,0): at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\SNI\SNIPacket.cs(280,0): at System.Data.SqlClient.SNI.SNIPacket.ReadFromStream(Stream stream)
D:\Projects\CoreFx\src\System.Data.SqlClient\src\System\Data\SqlClient\SNI\SNITcpHandle.cs(439,0): at System.Data.SqlClient.SNI.SNITCPHandle.Receive(SNIPacket& packet, Int32 timeoutInMilliseconds)
   ----- Inner Stack Trace -----
D:\Projects\CoreFx\src\System.Net.Sockets\src\System\Net\Sockets\Socket.cs(1394,0): at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
D:\Projects\CoreFx\src\System.Net.Sockets\src\System\Net\Sockets\NetworkStream.cs(295,0): at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

I found that the number of failure of DAC connection reduces after putting System.GC.Collect(); right before creating SqlConnection object when I tested.

SqlConnectionStringBuilder - several keywords are not supported

I am wondering why these keywords are marked as "not supported" in SqlConnectionStringBuilder? Is it by design or just is not yet implemented? Especially:

• 'Network Library'
• 'asynchronous processing'
• 'connection reset'

I tried to run mono System.Data tests using corefx sources (Passed: 2347, Failures: 36) and the most popular failure is
The keyword 'XX' is not supported on this platform.

For example, the following code works in .NET 4.6 but fails with NIE in .NET Core:

var builder = new SqlConnectionStringBuilder("SERVER=localhost;Network=DBMSSOCN");

SqlClient: OUTPUT clause causes exceptions to not get thrown until read

This scenario is a bit lengthy, so I'll load it into a gist: https://gist.github.com/benrr101/31628b57f1ab410090237c9018860e05

Basically what this boils down to is that if I .ExecuteReader() on a SqlCommand that has an OUTPUT clause in it, I don't get an exception from the query failing until I call .Read() on the SqlDataReader I gt back.

For instance executing:

INSERT INTO [dbo].[testtable]([small_col]) VALUES ("this value will be truncated")
SELECT * FROM [dbo].[testtable]

SqlDataReader reader = cmd.ExecuteReader(); // Doesn't throw
reader.Read() // DOES throw

But executing:

INSERT INTO [dbo].[testtable]([small_col]) OUTPUT inserted.[small_col] VALUES ("this value will be truncated")

SqlDataReader reader = cmd.ExecuteReader(); // DOES throw
reader.Read(); // Never executed

At this point I'm not sure if this is an expected behavior due to quirks in the TDS protocol or if it's a bug. Nevertheless, this is a bit of weird behavior that has already caused us a couple unexpected issue.

Connection pool fragmentation by connection string variation (case, space..etc)

Connection String A: Server=tcp:.;User ID=testuser;Password=test1234;pooling=true
Connection String B: Server=tcp:.;User ID=testuser;Password=test1234; pooling=true (notice a space where pooling=true starts)
Connection String C: Server=tcp:.;User ID=testuser;Password=test1234;pooling=True

Connection strings A, B, and C are indicating the same connection, but they creates separated connection pool for each of them although only one connection pool is supposed to be created.

Improve code coverage for System.Data.SqlClient (18.1%)

Report here

[SQL Server 2005] System.Data.SqlClient: pre-login fails with SSL error even with Encryption=false

Platform : Debian 8 "Jessie"
Runtime : 1.0.0-preview1-002702,coreclr,x64,linux
System.Data.SqlClient : 4.1.0-rc2-24027

ConnectionString : Server=tcp:server.domain.lan,1433;User ID=XXXX;Password=XXXX;Encrypt=False"

An attempt to connect to a Microsoft SQL Server 2005 instance results in the following exception:

Project foo (.NETCoreApp,Version=v1.0) was previously compiled. Skipping compilation.
Unhandled Exception: System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31 - Encryption(ssl/tls) handshake failed) ---> System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values.
Parameter name: size
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at System.Data.SqlClient.SNI.SslOverTdsStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.Stream.<>c.<BeginReadInternal>b__39_0(Object <arg>)
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Threading.Tasks.TaskToApm.End[TResult](IAsyncResult asyncResult)
   at System.Net.FixedSizeReader.ReadCallback(IAsyncResult transportResult)
--- End of stack trace from previous location where exception was thrown ---
   at System.Net.Security.SslState.InternalEndProcessAuthentication(LazyAsyncResult lazyResult)
   at System.Net.Security.SslState.EndProcessAuthentication(IAsyncResult result)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options)
   at System.Data.SqlClient.SNI.SNIProxy.EnableSsl(SNIHandle handle, UInt32 options)
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at ConsoleApplication.Program.Main(String[] args)

To me, the error looks as if the Encrypt=false just does not get communicated properly in the PRELOGIN packet because the exception is the same no matter if I set Encrypt to true (the default) or false.

Adding TrustServerCertificate=true with the Encrypt setting enabled or disabled has no effect.

The Encrypt setting is parsed as if I specify an invalid value for it, I get an error about it.

[Proposal] It should be possible to manually create SqlException

SqlException create methods are currently internal.
I would like some kind of create method to be public.

Rationale

Unit tests. There are various scenarios in which SqlException is handled differently than other kinds of exceptions, and being able to test those scenarios without actual database would be useful.

Related StackOverflow thread, How to throw a SqlException when needed for mocking and unit testing?, has 53 upvotes.

Potential API

class SqlException {
    // ...
    public SqlException(
        string message,
        SqlErrorCollection errorCollection,
        Guid clientConnectionId = Guid.Empty,
        Exception innerException = null
    )  { ... }
}

class SqlErrorCollection {
    // ...
    public SqlErrorCollection()  { ... }
    public SqlErrorCollection(params SqlError[] errors)  { ... }
}

class SqlError {
    // ...
    public SqlError(
        int infoNumber,
        byte errorState,
        byte errorClass,
        string server,
        string errorMessage,
        string procedure,
        int lineNumber
    ) { ... }
}

Open Questions

Is there a better way to expose SqlError that would allow easier future extensibility?
Some kind of SqlErrorBuilder?

System.Data.Sqlient isn't supported on this platform error in Update-Database

@deanilvincent commented on Tue Oct 10 2017

Title: System.Data.Sqlient isn't supported on this platform error

General

Environment specifications:
Visual Studio 2017 15.3
Installed 2.0 .Net Core

I'm trying to use the command "update-database -context NameOfMyContext" but somehow I got this error message that says "System.Data.Sqlient isn't supported on this platform error"

Here's my connection string

services.AddDbContext<CompanyDbContext>(options => options.UseSqlServer(@"data source=ipaddress;initial catalog=testingdbcore;user id=username;password=password;integrated security=false;"));

To add up, I successfully added a migration and everything is working fine but then suddenly when I trigger the command for updating my database, I got this issues.

Thank you in advance!

---

@Petermarcu commented on Tue Oct 10 2017

@divega would this be an issue that should be in the EF repo or @saurabh500 should this issue go to corefx?

---

@saurabh500 commented on Wed Oct 11 2017

It should go to Corefx

---

@deanilvincent commented on Wed Oct 11 2017

What should I do now guys? Is there any option?

---

@saurabh500 commented on Wed Oct 11 2017

What is your OS that you are running the above steps on @deanilvincent ?

---

@saurabh500 commented on Wed Oct 11 2017

@deanilvincent From the screen shot it is Windows.

Timestamp missing from SqlClientDiagnosticListenerExtensions.WriteCommandBefore telemetry publisher

The SqlBeforeExecuteCommand telemetry event is missing the timestamp making it more difficult to compute the duration of executed commands.

cc @saurabh500

SqlClient fails to conenct to Sql Server 2014 on Ubuntu 16.04 LTS and CentOS

When my DotNetCore app running on Dotnet core 1.0.1 tries to connect to Sql Server 2014 (SP2 with Cumulative Update 3 installed), I get the following exception

System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 35 - An internal exception was caught) ---> System.IO.IOException: Unable to write data to the transport connection: Connection reset by peer. ---> System.Net.Sockets.SocketException: Connection reset by peer
at System.Net.Sockets.Socket.Send(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
--- End of inner exception stack trace ---
at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at System.Data.SqlClient.SNI.SslOverTdsStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.Security.SslStreamInternal.StartWriting(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslStreamInternal.ProcessWrite(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Data.SqlClient.SNI.SNITCPHandle.Send(SNIPacket packet)
--- End of inner exception stack trace ---

The same app can connect to the Sql Server when running on a Mac.
The app can also connect when running inside a docker container on Mac. I used microsoft/aspnetcore:1.1.1 as the base image for the docker container.
However, on Ubuntu on CentOS based host systems, the same app running within docker fails to connect to the Sql Server.

Add Sql Impersonation Support

SqlClient team needs to add Impersonation support for Project K, but underlying code support does not currently exist.

Misleading ADP_OpenReaderExists exception message on MARS-disabled Sql Connection when incorrectly doing parallel requests

I've discovered the issue using ordinary .net4.5 on Windows but further investigating shows that it is related to code that now is in the .net Core

While simultaneousely (incrorrcetly) using single SqlConnection with Multiple Active Result Sets (MARS) disabled an exception with text

<data name="ADP_OpenReaderExists" xml:space="preserve">
<value>There is already an open DataReader associated with this Command which must be closed first.</value>
</data>

Note that text mentions same "Command" but in reality the resource taht is wrongly shared is not command but connection.

The exception is raised from https://github.com/dotnet/corefx/blob/cf19c22be88b5ea31b5e2ea69ab3632f930b1e26/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlInternalConnectionTds.cs#L587

The comment

// if MARS is on, then a datareader associated with the command exists
// or if MARS is off, then a datareader exists

above raising the exception is absolutely correct - if MARS is on the exception text is correct - problem is in using multiple readers with single command. But if mars is off the checks above checking for any active datareaders for the same connection, not only for datareaders for the same command.

So the problem is that information about what scope (Connection or Command) was checked is not passed to exception cinstructor and it incorrectly always says that there is another reader associated with the command

SqlClient: Allow |DataDirectory| macro in AttachDBFilename

Now that .NET Standard 2.0 includes AppDomain.GetData, there shouldn't be anything blocking the implementation to expand the |DataDirectory| substitution string in the AttachDBFilename value of the connection string.

SqlBulkCopy class lacks option to specify ORDER BY hint

Bulk insert into a table with PK is significantly faster if input data is in order (no need to write to tempdb first and run a sort). Need to add the ORDER(...) hint to the tdscommand string.

Failed to Connect to Sql Server with MultiSubnetFailover .netCore 2.0

I'm running a .NET Core App 2.0 in a container against a SqlServer 12 database with MultiSubnetFailover.

My connection string looks like this...

Data Source=..***.com;Initial Catalog=DATABASENAME;User ID=USER_ID;Password="USER_PASSWORD";Connect Timeout=60;MultiSubnetFailover=True

When i try to connect, it fails with the following error

Message="A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

Same issue is documented here for 1.1
https://github.com/dotnet/corefx/issues/14978

SqlClient in .Net Core is missing SqlDataSourceEnumerator

SqlClient doesn't have SqlDataSourceEnumerator implementation in .Net Core.

This should also be exposed via SqlClientFactory

EF Core SqlClient and App Insights

What can be the reason for not seeing raw SQL in EF app insights in net core project? Not locally nor in Azure.

I remember at some point with one of the RCs I saw SQL logged but not anymore.
Originally, I posted the question here dotnet/efcore#5510 but was referred to this repo.

SQL Server connection fails on centos,but succeeds on windows and in all other tools

I'm running a webapi project by dotnetcore on centos.but it is failed to connect to MSSQL.
In order to find the problem, I try to use the telnet to determine whether the server port, try to find the next is normal, and the project in the Windows environment are successful, the database can be connected, but deployed to centos is not connected to the database.

Error like this:
Microsoft.AspNetCore.Server.Kestrel[13]
Connection id "0HL30IKDGRVD6": An unhandled exception was thrown by the application.
System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 35 - An internal exception was caught) ---> System.AggregateException: One or more errors occurred. (No such device or address) ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException: No such device or address

Connection string example:
Data Source=192.168.1.5\SQLSERVER2008;Initial Catalog=AwardSysDB;User ID=xx;Password=xx;min pool size=100;max pool size=250;

Data Source=192.168.1.5,1435;Initial Catalog=AwardSysDB;User ID=xx;Password=xx;min pool size=100;max pool size=250;

Incorporate Misc code review comments for SqlClient

Please refer PR
dotnet/corefx#2986

The items to be taken care of,

1. Use utility methods.
2. Remove unnecessary base class calls
3. There is Obsolete SMI code which can be removed. Followup on the SMI v1 support and clean this up.

System.Data.SqlClient hangs on Linux (Docker) with SQL Server 2005

We are observing a wierd behaviour with System.Data.SqlClient and Linux (Debian Jessie / Docker with microsoft/aspnetcore:1.1.0). When connecting to an SQL Server 2016 instance everything works fine. When connecting to SQL Server 2005, our application hangs indefinitely. Sometimes, an InvalidOperationException occurs stating: "The timeout period elapsed prior to obtaining a connection from the pool.". Everything works as expected on Windows.

Maybe a similar issue to #15287?

Could not load type 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data, Version=4.0.0.0

I am getting this exception when i tried to restore the Data base using c# , .Net core2 and VS2017 preview2 ,
here is the sample code
SqlConnection sqlConnection = new SqlConnection(ConnectionString);
// Error on the line below
Microsoft.SqlServer.Management.Common.ServerConnection conn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);

but this is working in console application . Did i miss anything ...

Thanks
Victor.A

Connections from the pool are not reused when using async methods in parallel

Moved from dotnet/efcore#10169 reported by @wertzui

Whem I'm using async methods (like FirstOrDefaultAsync) to get a result from the Database inside a Parallel.For, the connections are not correctly reused from the connection pool.

The connections will rise to the configured Max Pool Size and after some time (around 1-2 minutes on my machine using localdb), exceptions will be thrown.

Using either async methods and a normal for-loop or non-async methods an a Parallel.For-loop, the connections are properly reused and I can observe that a normal for-loop uses 1 connection and a Parallel.For-loop uses 4 connections which corresponds to MaxDegreeOfParallelism.

Exception message:
System.InvalidOperationException occurred
  HResult=0x80131509
  Message=Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
Stack trace:
   at System.Data.Common.ADP.ExceptionWithStackTrace(Exception e)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.<OpenAsync>d__31.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<BufferlessMoveNext>d__9.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<ExecuteImplementationAsync>d__33`2.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<ExecuteImplementationAsync>d__33`2.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<MoveNext>d__8.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.<_FirstOrDefault>d__82`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.TaskResultAsyncEnumerable`1.Enumerator.<MoveNext>d__3.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at EfPoolingTest.UnitTest1.<>c.<NormalContextParallel>b__4_0(Int32 i) in C:\Users\xxx\documents\visual studio 2017\Projects\EfPoolingTest\EfPoolingTest\UnitTest1.cs:line 70
   at System.Threading.Tasks.Parallel.<>c__DisplayClass19_0`1.<ForWorker>b__1(RangeWorker& currentWorker, Int32 timeout, Boolean& replicationDelegateYieldedBeforeCompletion)

Steps to reproduce

• Clone https://github.com/wertzui/EfPoolingTest and open the project
• Run the "ViewConnections.sql" script to see that there are no connections open (beside the 2 used for the script)
• Run the test "NormalContextParallelAsyncConfigureAwaitFalse" (I suggest running in Debug mode, so you can see the output)
• Run the "ViewConnections.sql" script multiple times to see the connections increase.
• After some time you will also get an exception
• Run the other tests and observe the connection count

Further technical details

EF Core version: 2.0.0 (also tested with 1.1.3)
Database Provider: Microsoft.EntityFrameworkCore.SqlServer/localdb
Operating system: Windows 10 FCU
IDE: Visual Studio 2017 15.4.1

Backport Mars cleanup fix PR #22709 to release/2.0.0

This change should be backported to 2.0.0, if possible.

PR: dotnet/corefx#22709
Original issue: https://github.com/dotnet/corefx/issues/22661

Suspicious error object creation in System.Data.SqlClient.TdsParserStateObject.OnTimeout()

System.Data.SqlClient.TdsParserStateObject.OnTimeout() goes like this:

   _internalTimeout = true;
     // lock protects against Close and Cancel
     lock (this)
     {
         if (!_attentionSent)
        {
             AddError(new SqlError( WHATEVER ));

If you look through usage of _internalTimeout elsewhere in the same class you may notice that ReadSniError() also checks _internalTimeout and optionally sets fail local variable. It's unclear whether this may cause ThrowExceptionAndWarning() being called with empty "errors" collection which would cause https://github.com/dotnet/corefx/issues/6119 I guess that OnTimeout() should add a new SqlError() no matter if _attentionSent is set.

Add Tracing in SqlClient

SqlClient doesn't have any tracing in .Net Core. All the Bid Tracing API calls were removed during the port to Core.
One of the options is to use EventSource to emit traces during runtime.
Something similar was done by @stephentoub in System.Data.Common .
Ref: https://github.com/dotnet/corefx/blob/master/src/System.Data.Common/src/System/Data/Common/DataCommonEventSource.cs

cc @corivera

SqlClient diagnostics Before event should record timestamp

The following logging of the before event for SqlClient diagnostics should include a timestamp member. This would be as simple as adding Timestamp = Stopwatch.GetTimestamp() to the anonymous type passed into DiagnosticListener.Write().

Please assign this issue to me, as I will be making the code change and submitting the pull request (I am the author of the code in question).

System.Data.SqlClient.SqlException: The connection is broken and recovery is not possible

From @robbpriestley on March 23, 2016 19:27

Hello,

I'm pretty new to EF and .NET Core, but I just thought I'd report an issue I had today and have been able to diagnose somewhat. It has to do with the runtime and applying a migration. In my ASP.NET Core project, when I removed the dnx451 framework from project.json (intending instead to rely entirely on the dnxcore50 framework) and I perform the following actions:

dnvm use default -r coreclr
dnx ef database update

I consistently get the below exception. To resolve, I need to restore the dnx451 framework in the project.json and use the mono runtime. Then, it works.

My database is SQL Server running on Amazon RDS.

Exception message:

Applying migration '20160323185819_Update1'.
System.Data.SqlClient.SqlException: The connection is broken and recovery is not possible.  The connection is marked by the server as unrecoverable.  No attempt was made to restore the connection.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlConnection.ValidateAndReconnect(Action beforeDisconnect, Int32 timeout)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.Data.Entity.Storage.Internal.RelationalCommand.<>c.<ExecuteNonQuery>b__13_0(DbCommand cmd, IRelationalConnection con)
   at Microsoft.Data.Entity.Storage.Internal.RelationalCommand.Execute[T](IRelationalConnection connection, Func`3 action, String executeMethod, Boolean openConnection, Boolean closeConnection)
   at Microsoft.Data.Entity.Storage.RelationalCommandExtensions.ExecuteNonQuery(IEnumerable`1 commands, IRelationalConnection connection)
   at Microsoft.Data.Entity.Migrations.Internal.Migrator.Execute(IEnumerable`1 relationalCommands)
   at Microsoft.Data.Entity.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.Data.Entity.Design.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.Data.Entity.Commands.Program.Executor.Execute(Action action)
ClientConnectionId:7f7dac22-d9a0-49cc-950b-5a532b754754
The connection is broken and recovery is not possible.  The connection is marked by the server as unrecoverable.  No attempt was made to restore the connection.

Copied from original issue: dotnet/efcore#4891

Add TestCommandBuilder() and TestInitialCatalogStandardValues() in SqlSchemaInfoTest

TestCommandBuilder() and TestInitialCatalogStandardValues() need to be filled out after CommandBuilder and PropertyDescriptor become supported in .NET Core.

SqlClient: Command execution failures when using Packet Size = 16388 with Encryption

When setting the packet size to 16388 or higher while using encryption, command execution fails. This issue is present with both managed and native SNI, and with TCP & NP. Failures also happen with sync & async command execution.

With native SNI:
System.Data.SqlClient.SqlException : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
---- System.ComponentModel.Win32Exception : The specified network name is no longer available

With managed SNI:
System.Data.SqlClient.SqlException : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 35 - An internal exception was caught)
---- System.IO.IOException : Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
-------- System.Net.Sockets.SocketException : An existing connection was forcibly closed by the remote host

CC @saurabh500 @geleems

Investigate if lock statements are necessary in SqlClient.SNITcpHandle's EnableSsl & DisableSsl methods

Whenever reads and writes are called on the _stream variable in SNITcpHandle, a lock(this) statement is used. EnableSsl & DisableSsl set the value of _stream without using any lock statements.

CC @saurabh500

SqlFileStream roadmap

Hello
I'd like to ask are there any plans to implement SqlFileStream. At this moment this is the only part that makes me to stop migrate to core base.

sqlclient does not close connections in Docker(debian)

We are seeing behaviour form the System.Data.Sqlclient is not closing the connections when its running on the official mircosoft/dotnet docker image for linux. The connections are accumulating to over 400 connections in a couple of days on a service with little traffic. We have the same code running on a windows platform and there we do not see this behaviour.
We are running dotnet 1.1.0.