I have a problem where I can not set the language of a db-user that is currently active in an integration test scenario of a 4.8 server, that is sensitive to the users language (which it obviously shouldn't be, but that's legacy code for you) due to issues with the date format.

Specifically no matter where I apply it, the following statement does still cause issues with the date format, when I change the language from us_english to Deutsch (German) in the same SQL-transaction:

SET LANGUAGE Deutsch;

Because of this I tried to work around this by setting the default language of the user and afterwards initiating a transaction:

USE [master]; 
ALTER LOGIN [myuser] WITH DEFAULT_LANGUAGE=[Deutsch];

This successfully changes the language of the user but causes the following errors:

02/18/2025 12:53:09,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 28.
02/18/2025 12:53:09,Logon,Unknown,Login failed for user 'myuser'. Reason: Failed to determine the language and date format while revalidating the login on the connection. [CLIENT: <named pipe>]
02/18/2025 12:53:09,spid71,Unknown,Error: 18056<c/> Severity: 20<c/> State: 28.
02/18/2025 12:53:09,spid71,Unknown,The client was unable to reuse a session with SPID 71<c/> which had been reset for connection pooling. The failure ID is 28. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

This change is done programatically like so:

public void ChangeLanguage()
{
    ExecuteSql($"USE [master]; ALTER LOGIN [myuser] WITH DEFAULT_LANGUAGE=[Deutsch]");
}

private void ExecuteSql(string sql)
{
    string cs = $@"Data Source={sqlServerName};Integrated Security=True;Pooling=False";
    using (SqlConnection c = new SqlConnection(cs))
    {
        try
        {
            using (SqlCommand cmd = c.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                c.Open();
                cmd.ExecuteNonQuery();
            }
        }
        finally
        {
            c.Close();
        }
    }
}

The connection string used for the transactions afterwards is constructed like so:

connectionString = $"server={sqlServerName};database={testDbName};user id={testDbUsername};password={testDbPassword};";

Is there a "short and sweet" reliable way to change a users language like this?

I would also appreciate any suggestions on why "SET LANGUAGE" may not have worked.

I have a problem where I can not set the language of a db-user that is currently active in an integration test scenario of a 4.8 server, that is sensitive to the users language (which it obviously shouldn't be, but that's legacy code for you) due to issues with the date format.

Specifically no matter where I apply it, the following statement does still cause issues with the date format, when I change the language from us_english to Deutsch (German) in the same SQL-transaction:

SET LANGUAGE Deutsch;

Because of this I tried to work around this by setting the default language of the user and afterwards initiating a transaction:

USE [master]; 
ALTER LOGIN [myuser] WITH DEFAULT_LANGUAGE=[Deutsch];

This successfully changes the language of the user but causes the following errors:

02/18/2025 12:53:09,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 28.
02/18/2025 12:53:09,Logon,Unknown,Login failed for user 'myuser'. Reason: Failed to determine the language and date format while revalidating the login on the connection. [CLIENT: <named pipe>]
02/18/2025 12:53:09,spid71,Unknown,Error: 18056<c/> Severity: 20<c/> State: 28.
02/18/2025 12:53:09,spid71,Unknown,The client was unable to reuse a session with SPID 71<c/> which had been reset for connection pooling. The failure ID is 28. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

This change is done programatically like so:

public void ChangeLanguage()
{
    ExecuteSql($"USE [master]; ALTER LOGIN [myuser] WITH DEFAULT_LANGUAGE=[Deutsch]");
}

private void ExecuteSql(string sql)
{
    string cs = $@"Data Source={sqlServerName};Integrated Security=True;Pooling=False";
    using (SqlConnection c = new SqlConnection(cs))
    {
        try
        {
            using (SqlCommand cmd = c.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                c.Open();
                cmd.ExecuteNonQuery();
            }
        }
        finally
        {
            c.Close();
        }
    }
}

The connection string used for the transactions afterwards is constructed like so:

connectionString = $"server={sqlServerName};database={testDbName};user id={testDbUsername};password={testDbPassword};";

Is there a "short and sweet" reliable way to change a users language like this?

I would also appreciate any suggestions on why "SET LANGUAGE" may not have worked.

• c#
• sql-server

• From the documentation DEFAULT_LANGUAGE = language Specifies a default language to be assigned to the login. The default language for all SQL Database logins is English and can't be changed. The default language of the sa login on SQL Server on Linux is English, but it can be changed. So according to the docs it should not work. – Ralf Commented 2 days ago
• @Ralf Maybe I'm using an older server version, but the ALTER LOGIN ... command lets me set a different language. This change is also visible in SSMS afterwards. I'd assume the docs are badly worded or just plain wrong. – Allu Commented yesterday

2 Answers 2

The error messages are not very clear to me, but after some testing I was able to deduce that it is related to connections being reused due to pooling, which causes an invalid state when the users language was changed.

A possible solution would be disabling pooling for the "regular" connection string MSDN:

connectionString = $"server={sqlServerName};database={testDbName};user id={testDbUsername};password={testDbPassword};Pooling=false";

Or resetting the connection pool programatically:

public void ChangeLanguage()
{
    ExecuteSql($"USE [master]; ALTER LOGIN [myuser] WITH DEFAULT_LANGUAGE=[Deutsch]");
    SqlConnection.ClearAllPools();
}

For me personally the second option worked well, since disabling pooling completely would impact performance of the whole test-suite negatively.

You can set the language on your ConnectionString using the Language attribute, something like:

 string cs = $@"Data Source={sqlServerName};Integrated Security=True;Language=german";

and it will set it to the session language without any workarounds.

See https://learn.microsoft/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=net-9.0-pp