SCCM Prerequisite Required SQL Server Collation

SCCM Prerequisite Required SQL Server Collation The SQL server collation acts as the default collation for all system databases that are installed with the instance of SQL Server and also any newly created user databases. Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales. The Server collation is specified during SQL Server installation. This post will help anyone who is looking to fix the required SQL server collation prerequisite check that is seen while running prerequisite check during SCCM installation.

SCCM Prerequisite Required SQL Server Collation

When you install SCCM, during the prerequisite check step, it verifies that the instance for SQL Server and the Configuration Manager site database is configured to use the SQL_Latin1_General_CP1_CI_AS collation, unless you are using a Chinese operating system and require GB18030 support. We should specify SQL Server Collation when installing the SQL Server. The collation should be set to SQL_Latin1_General_CP1_CI_AS.

SCCM Prerequisite Required SQL Server Collation

But sometimes during the SQL installation, you miss the step for specifying SQL Server Collation. If SQL Server is installed with a different collation for example Latin1_General_CI_AS it won’t work. During the SCCM prerequisite check, you get the below message.

Configuration Manager requires that you configure your SQL Server instance and Configuration Manager site database (if already present) to use the SQL_Latin1_General_CP1_CI_AS collation, unless you are using a Chinese operating system and require GB18030 support. For information about changing your SQL Server instance and database collations, see http://go.microsoft.com/fwlink/p/?LinkID=234541. For information about enabling GB18030 support, see http://go.microsoft.com/fwlink/p/?LinkId=234542.

SCCM Prerequisite Required SQL Server Collation

Few things you need to keep in mind here –

  1. Instead of changing the default collation of an instance of SQL Server, you can specify a default collation for each new database you create. This is recommended by Microsoft.
  2. /SQLCOLLATION parameter should be supplied in the command. If you don’t supply this parameter, then SQL Server will rebuild the system databases with the collation you selected when you installed SQL Server.
  3. Use a dedicated instance of SQL Server for each site.
  4. While changing the SQL Collation, use an account which has admin rights on SQL server.

To change the SQL Collation, you need to rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. Launch the command prompt (run as administrator). Navigate to your SQL setup folder or DVD where the setup file is located and execute the following command.

Setup.exe /QUIET /ACTION=REBUILDDATABASE  /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Account Name

Command Info

/q – performs silent installation.

/Action=RebuildDatabase – This option rebuilds the system databases to change the collation name.

/INSTANCENAME – Provide the name of the instance for which the collation has to be changed. If you have a Default Instance then it is “MSSQLSERVER”. If you have setup Named Instance then use “Named Instance Name”

/SQLCollation – Provide the new collation name of SQL Server.

/SQLSYSADMINACCOUNTS – Provide account name which has admin rights on SQL server.

When setup has completed rebuilding the system databases, it will return to the command prompt with no messages. So when you execute the above command there is no message that shows about the collation being changed. To verify or view a collation setting for a database, open the SQL Server Management Studio, connect to an instance of Database Engine and then expand that instance. Expand Databases, right-click the database and select Properties. On the General page, check the collation info under Maintenance option.

SCCM Prerequisite Required SQL Server Collation

Once you change the SQL Collation, run the prerequisite check again and the SQL Server Collation message shouldn’t be seen again.

SCCM Prerequisite Required SQL Server Collation

21 Comments

  1. Thanks! I keep getting the dedicated sql instance prereq failure on re-installation, have followed several recommendations to delete the SMS keys in the registry however when I do they repopulate almost immediately

  2. Thank you so much!!! This worked EXACTLY how it should.

  3. Avatar photo amine lallali says:

    thank you so much. you are so helpful. I really appreciate your effort Sir 🙂 Love love love love

  4. Thanks, Prajwal,
    Ran into so same problems, find your site through the internet. Great guide, thanks

  5. While using your amazing guides, I ran into this issue and funny enough, a search on Google brought me back onto your website to solve my problem. Thank you very much for all your efforts!

  6. Hi, Prajwal. I had ran this command: Setup.exe /QUIET /ACTION=REBUILDDATABASE /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS /INSTANCENAME=CM_XXX /SQLSYSADMINACCOUNTS=DomainAdminAccount. Did not get any error, but when I check in Management Studio, it did not change. Anything I did wrong?

    1. have you change instancename and sqlsysadminaccounts ?

    2. Avatar photo Russell Baker says:

      Add this to command line: /sapwd=yourpassword

  7. simply fantastic guides completes and made professionally

  8. Great Fix! It solved my problem and you provided great clarification on the error.

    Thanks!

  9. Excelente you are the best! Thanks for all

  10. Avatar photo Francis Bonnamour says:

    thank you for your web site

  11. Avatar photo ankur Pathak says:

    Fantastic…This is what I was looking for

  12. Avatar photo ankur Pathak says:

    Fantastic This is what i was looking for…

Leave a Reply

Your email address will not be published. Required fields are marked *