Collation not supported with .NET 6 and German_Phonebook

[Tatenshi]

Describe the bug

When executing a simple select using .NET 6 and the Collation German_PhoneBook_CI_AI the Exception "The Collation specified by SQL Server is not supported" is thrown. With .NET 5 as selected TargetFramework or when another Collation is selected (Tested with Greek and Latin1) it works.

I have tested it with SQLServer Version 14.0.1000.169, 14.0.1000, 14.0.3411 and 15.0.4138.2.

The Collation of the SQLServer is Latin1_General_CI_AS and the Collation the Table is German_PhoneBook_CI_AI.

Am i doing something wrong here?

Exception message: Microsoft.Data.SqlClient.SqlException: 'The Collation specified by SQL Server is not supported.'
Stack trace:
This exception was originally thrown at this call stack:
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParser.DrainData(TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParser.ThrowUnsupportedCollationEncountered(TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp, String& value)
   at Microsoft.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName, SqlCommand command)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)
   at Microsoft.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
   at Microsoft.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
   at Microsoft.Data.SqlClient.SqlDataReader.get_Item(Int32 i)
   at TestProject.Program.Main(String[] args) in C:\Users\Franz\source\repos\TestProject\TestProject\Program.cs:line 21

To reproduce

This is a Console Application that i used to reproduce the issue.

using Microsoft.Data.SqlClient;
using System;

namespace TestProject
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var oConn = new SqlConnection("Server=*SERVERNAME*;Database=*SOMEDATABASE*;Trusted_Connection=True;");
            oConn.Open();
            var Command = oConn.CreateCommand();
            Command.CommandText = "select 'TEST'";
            var oReader = Command.ExecuteReader();
            while(oReader.Read())
            {
                for (int i = 0; i < oReader.FieldCount; i++)
                {
                    Console.WriteLine(oReader[i]);
                }
            }
            oConn.Close();
        }
    }
}

Expected behavior

"TEST" is printed on the console without an exception

Further technical details

Microsoft.Data.SqlClient version: 3.0.1 and 4.0.0 preview 2
.NET target: .NET 5, .NET 6
SQL Server version: Version 14 and 15
Operating system: Windows 10 21H1

[JRahnama]

@Tatenshi, thanks for bringing the issue up. Just to clarify, with setting the Collation of the SQLServer to Latin1_General_CI_AS and the Collation of the Table to German_PhoneBook_CI_AI I would be able to see the issue. Is that correct?

Have you tried with netcoreapp3.1 to see if the issue comes up?

[Tatenshi]

When i change the Database Collation to Greek or any other Collation i have tested, the issue disappears. So i would think, that you should be able to see the issue with the configuration you mentioned.

I tried netcoreapp3.1 and it works just as net5.0. It seems like its just net6.0 that has the issue.

[Wraith2]

https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15 doesn't list that encoding, it only lists the AS not AI version.

[Tatenshi]

I just tested it with ver15 and the AS Database Collation (German_PhoneBook_CI_AS). There the issue also appears.

[Kaur-Parminder]

Closing this issue as this issue is similar to #1396 Please see the discussion there.