'Changed default collation to "Latin1_General_CP1_CS_AS" gives The login failed. Login failed for user 'sa'

Facing the following error when using the different collation than the default SQL server collation & installing the installer,

[EXCEPTION] ExecuteSQLConnection SQLException Errors : Index #0 Message: Cannot open database "testadmin" requested by the login. The login failed. LineNumber: 65536 Source: .Net SqlClient Data Provider Procedure: Index #1 Message: Login failed for user 'sa'. LineNumber: 65536 Source: .Net SqlClient Data Provider Procedure:

Our work flow: we are having an installer which will execute the DB scripts & set up the web applications to run. The SQL scripts are used to create new logins [some users for application purpose], databases [new databases for product], etc.

Our environment: Windows server 2016 [changed to French language], SQL server 2012 express with different collation used "Latin1_General_CP1_CS_AS" from default one "Latin1_General_CP1_CI_AS"

So when the installer executes the SQL scripts one by one [with sa user] to set up the DB components. During the DB component installation, got the above error while assigning the logins "dbadminuser" & "aduser" with the below sql statements

USE [TestAdmin]
GO
IF not  EXISTS (SELECT 1  FROM sys.database_principals WHERE name = N'dbadminuser')
begin
    CREATE USER [dbadminuser] FOR LOGIN [dbadminuser]
    EXEC sp_addrolemember N'db_owner', N'dbadminuser'
end

IF not EXISTS (SELECT 1  FROM sys.database_principals WHERE name = N'dbuser')
begin
    CREATE USER [dbuser] FOR LOGIN [dbuser]
    EXEC sp_addrolemember N'db_owner', N'dbuser'
end
Go

during this sql statements execution, the database TestAdmin already created. seems like case sensitive issue wrt DB name???

Provide any suggestions to handle the collation situation either from SQL side or C# side?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source