Friday 18 November 2016

FIX: The request does not contain a certificate template extension or the CertificateTemplate request attribute

Scenario:

You are trying to generate an SSL Certificate from your Enterprise CA using the MMC Snapin using a CSR File and you get the error:

—————————
Certificate Request Processor
—————————
The request contains no certificate template information. 0x80094801 (-2146875391)
Denied by Policy Module 0x80094801, The request does not contain a certificate template extension or the CertificateTemplate request attribute.
—————————
OK
—————————

Fix:

Generate your CSR file as usual from IIS Management Console on your Web Server.
NB - You will now need to make sure that the Encryption is at least 2048 when generating the CSR...

Copy this csr file to a folder on your Enterprise CA Folder like C:\CertReq\

Log onto your Enterprise CA with Domain Administrator Account and run a command prompt with Administrative Privileges and type the following command:

C:\>certreq -submit -attrib "CertificateTemplate:WebServer" "C:\CertReq\CSR_File.txt" and press enter...
 You will then be prompted with a dialogue box to choose your Enterprise CA...

 Once you have clicked OK you will be prompted to save the Certificate File, once this is done you should see something like the following:



Now this is done copy your Certificate file to the Web Server and Complete Certificate Request in the IIS Management Snapin...


Thursday 7 April 2016

Check Articles on a Publication Database

This needs to be run against the Publication Database:

This script came from Stack Overflow User Davmos:

http://stackoverflow.com/users/493680/davmos

SELECT 
  msp.publication AS PublicationName,
  msa.publisher_db AS DatabaseName,
  msa.article AS ArticleName,
  msa.source_owner AS SchemaName,
  msa.source_object AS TableName
FROM distribution.dbo.MSarticles msa
JOIN distribution.dbo.MSpublications msp ON msa.publication_id = msp.publication_id
ORDER BY 
  msp.publication, 
  msa.article

Friday 4 March 2016

List users with roles on a Database



SELECT
        [UserType] = CASE membprinc.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
                     END,
        [DatabaseUserName] = membprinc.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = roleprinc.[name]
FROM
--Role/member associations
  sys.database_role_members AS members
--Roles
JOIN sys.database_principals AS roleprinc 
ON roleprinc.[principal_id] = members.[role_principal_id]
--Role members (database users)
JOIN sys.database_principals AS membprinc 
ON membprinc.[principal_id] = members.[member_principal_id]
--Login accounts
LEFT JOIN sys.server_principals AS ulogin    
ON ulogin.[sid] = membprinc.[sid]
WHERE membprinc.[type] IN ('S','U','G')
-- No need for these system accounts
AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')