Have a look at the catalog of check plugins: Check Plug-Ins Catalog
The checks need the Checkmk mssql.vbs plugin installed in the agent.
Usually the SYSTEM account the checkmk agent is running under has sufficient rights to connect to the SQL server.
More details can be found in the header of the plugin: checkmk/mssql.vbs at master · tribe29/checkmk · GitHub