Saturday, November 16, 2019

SQL Server Linked Server error - OLE DB provider "ADSDSOObject" for linked server "ADSI" reported an error.

I ran into this problem while trying to set up a linked server from SQL server(SQL 2016 Developer Edition) to Active Directory. More information how to do that can be found here

"The OLE DB provider "ADSDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation."

After googling and binging and scratching my head, it turned out that I misspelled the domain name in the user name for the LDAP connection. As I found many other solutions for this problem but no one mentioned a typo I wanted to share this. The OLE DB provider is giving only a little information back about the real problem.

Friday, November 16, 2018

Fun with bitmask math

I had fun today working on queries against Active Directory userAccountControl. In cases like mine where you have 514 and 546 for enabled and disabled accounts, I wanted to check which bit is set.
so 2 to the power of 2 is set if the account is disabled.

see here for more values to play with

the bitmask.ps1 script gives me all the bits set


$hexvalue = '{0:X}' -f $value
$bitmask = [convert]::tostring($value,2)
write-host "hex original value is 0x$($hexvalue)"
write-host "dec original value is $value"
write-host "bin original value is $bitmask"

do {

$valcheck = [math]::pow(2,$i)

if ( $value -band $valcheck) { write-host "hit: 2 to the power of $i = $valcheck"}

} until ($i -eq 31)
write-host "info: max tested to 2 to the power of 30, the max value for a int32 value"

In my case, I have userAccountControl as an integer value in a SQL database and I can run the following query to find all objects which are disabled, regardless if it is 514 or 546

select cn, useraccountcontrol
from [dbo].[domain.com_users]
where useraccountcontrol & 2 = 2

Saturday, January 21, 2017

What groups are used in my Microsoft Identity Manager installation

I came across a question today what the actual group names are used in a MIM installation. For a production system that should never be a question but in test labs with one AD and multiple "flying" setups, it can understand that it come sometimes to confusion. So thought it can be helpful to others to show how to find that out.

At first, I was looking into the registry under the FIMsync service but there was nothing to find there.
So I check in the sync database and voila I found the SIDs in the FIMsynchronization database in table [mms_server_configuration]. I copied the value from administrators_sid and pasted it into ldp.exe to run the translation to a human-readable group name. But bummer. The sid format looked already a little bit strange and ldp.exe could not use it as well.


I know ldp.exe can use a SID in the format S-1-5-...and so I googled it and found on script to convert the hex sid into the format ldp.exe (and I) like.


So now I know the group name is TESTLAB\LAB4711-MIMSyncAdmins ;-)

Tuesday, May 31, 2016

Help on The federation server proxy could not renew its trust with the Federation Service. ADFS 3.0

If you run a Windows Server 2012 R2 ADFS with Web Application Proxy (WAP) you may run into a situation where the web browser page is just saying

The page cannot be displayed because an internal server error has occurred.

and in the event log of the server running WAP you see this error (and many more afterwards)

Eventlog (Apps and Services/AD FS/Admin)
The federation server proxy could not renew its trust with the Federation Service.

So that means the trust relationship between WAP and the ADFS is broken. So you can uninstall WAP from that machine and reinstall it. the install wizard will guide you to reconnect to the ADFS server or you run the following commands to re-instate the trust.

 dir Cert:\LocalMachine\My

dir Cert: .... will list all certificates available to connect to the ADFS server. select the one with the name of the ADFS in it if you see multiple certs. You will need the thumbprint of the certificate later in this process

 $ADFScredentials = Get-Credential

Enter here your admin user on the ADFS server

Then enter the Install-WebApplicationProxy command, note there is no Reconnect or Renew command let, to re-establish the trust relationship to the ADFS server.

 Install-WebApplicationProxy -CertificateThumbprint CC35E9EE9EBA26697ABDC9C74CC4218818B1D1B8 -FederationServiceName "" -FederationServiceTrustCredential $ADFScredentials

I blog this here for my own notes but I hope it will help others as well.

Monday, April 4, 2016

How to protectet credentials with certificates

I am at the PowerShell and DevOps Global Summit 2016 in Bellevue, WA this week and I attended a session ("A DevOps DSC Crash Course ") from and with Jason Helmick yesterday where he pointed out that with PowerShell 5 Microsoft is changing the approach in PowerShell and you know DSC is PowerShell to protect usernames and passwords in a better way by asking us to use certificates to encrypt that information. I hope the information in this post will help you to retrieve such an certificate and to demystify a few things.

For DSC this certificate has to be installed in the machine certificate store and the certificate including the private key has to be copied to all machines where you want decrypt that information.

first of all, what are the certificate requirements:
- it is mandatory that this certificate contains the OID, that is the OID for extended key usage for "Document encryption"
- As any other certificate that certificate is verified, so it must be trusted. A good way to achieve that is get a certificate from an internal PKI
- the certificate must be installed for DSC in the local machine certificate store, not in the user store. But it does not mean that this certificate is machine specific. So it should not include the OID for client authentication and server authentication.
- also the name on the certificate should be something what lets you distinguished later between a real machine cert and that certificate. also you can have multiple certificates for different machines in your DSC environment, so think about a good naming convention.
- as you protect data with that certificate you should enable key recovery on your internal PKI to have always a copy of the private key or you should keep a copy of the private key or the passwords (perhaps you have them in a password vault anyway) in a safe place somewhere else. As a PKI guy I recommend to turn on key archival regardless what other measurements you implement.
- You want a SHA2 certificate as SHA1 is on the deprecate list
- from the handling/management of such an certificate a lot of things are very similar to SSL/TLS certificates. We cannot use auto-enrollment as it has to be the same key on all the machines and we do not want a auto-renewal either. The common name in the certificate is an alias, a friendly name like webfarm0815 and the cert is installed on multiple machines like you would do that in a web farm environment where not the load balancer is the SSL/TLS endpoint.

lets switch to the PKI side of the house:
- The Windows CA (Active Directory Certificate Services ADCS) does not have a default template for our purpose but has already included the OID for Document Encryption, so it is only a click away to add it to an certificate template.

- go the the Certificate Template MMC (certtmpl.msc)
- make a copy of the Web Server template
- name it e.g. Cred_encryption
- check the validity and maybe 3 years is a good value
- set Allow private key to be exported
- make sure that the minimum key size is at least 2048 bit, don't go higher than 4096 for performance reasons and compatibility
- the common name needs to be provided during submitting the certificate request, that is the default on the web server template
- now comes the important part. Remove Server authentication from the extended key usage attribute and add Document encryption

- make proper settings to allow users to request such an certificates and publish the certificate template on the CA.

Now we can request a certificate using certreq.exe

- create a inf file, e.g. Cred_encryption.inf

ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
ProviderType = 12
RequestType = CMC


CertificateTemplate = Cred_encryption

And here we are with the batch file, to complete this post.

certreq -f -NEW Cred_encryption.inf Cred_encryption.csr
CertReq -f -Submit Cred_encryption.csr Cred_encryption.cer
CertReq -accept Cred_encryption.cer

Now you can export the certificate including the private key from the local machine certificate store.

here an idea how to do that with PowerShell

PS C:> $outpath = Get-Location;
PS C:> $pass = read-host "pass" -assecurestring;

PS C:> dir cert:\LocalMachine\my |
Where-Object { $_.HasPrivateKey -and $_.PrivateKey.CspKeyContainerInfo.Exportable } |
Foreach-Object { [system.IO.file]::WriteAllBytes( "$outpath\$($_.thumbprint).pfx" , ($_.Export('PFX', $pass)) ) }

More questions/topics (I will try to answer these in future posts):
- what happens if that certificate expires or is revoked? as in any other case of using certificates to encrypt data you should be able to decrypt the data even the certificate has expired or is revoked. I can't answer yet if DSC would perform a certificate check before using it. hopefully not. Only the PowerShell command to encrypt the data should give you at least a warning.
(Remote Desktop Connection Manager 2.7 can use such a certificate as well, if it is installed in the user store and it can still be used to decrypt data but you cannot select an expired certificate for encryption)
- How to rollover encrypted credentials from certificate A to certificate? In theory the same data can be encrypted with multiple certificates. If that is supported=working with DSC that would ease the process to move to a newer certificate and only in case the certificate was revoked you want remove access. Other than that we can write a PowerShell script to decrypt the data and re-encrypt the data with the new certificates. This is also known as re-keying data. Or you just create a new list of credentials from you password vault.
- Even the setting do not export on a certificate is not a 100 percent secure we should add set it on all machines. So that means we need to get it from the CA with private key set to exportable, but then on the import to all remote machines it is imported with the flag not exportable. We want DSC to be able to use the certificate on those machines but we do not want anyone else being able to copy it and take it home including the encrypted file holding the credentials.

Until next time,

Friday, April 1, 2016

When does a Windows CA refreshes the CRL?

Hi, since I am working with Windows PKI (10+ years) I noticed that a Windows CA is running into a situation where the CRL has expired and the CA service will never renew it until you force it to do.
That happens if the CA is down for maintenance or restore right at the time the renew of the CRL would be triggered. So that is bad but only things we cannot change keep us awake at night and so here is the solution for that problem.
I recommend to issue the CRL e.g. for issuing CAs with a validity between 10 and 14 days, no longer and definitely not much shorter until you are 110 percent sure about you recovery process and you have tested it in real before. I had once a client they promised to have any machine recovered within 4 hours. We had a bad hard drive or controller and one came to the other. We asked for a recovery what happened 4 days later. Okay so if your CRL is to short you get a lot of pressure at this point.

As in any good PKI project you can ask certutil.exe for help.

A certutil.exe -CRL command triggers the CA to issue a new CRL. Depending on your configuration that file goes into LDAP and/or on the file system. With certutil.exe -getcrl myCAcrlfile.crl you get it from the CA as well e.g. in case you want copy it to multiple web servers.
That can do a scheduled tasks e.g. executed once a day or night

So that gives us some advantages:
1) we see every day a new CRL (like a heartbeat ;-))
2) the new CRL has always the max validity period
3) is contains the latest revoked certificates, if any
4) even the CA was down for maintenance at the point of CRL renewal,   the schedule task will take care of the CRL renewal

If you make use of certificate revocation a lot that is also the way to trigger the CA to issue new CRLs more often. Just run it more often.

Until next time,

Thursday, March 31, 2016

certutil.exe shortcut

currently I am working with great people on a PKI project. So it came how it has to come, we use certutil.exe for various purposes and we came across a nice shortcut I want share.
So every time you use certutil.exe to query a CA server from remote you have to specify the config information. (Actually the -ping allows you just to specify the CA hostname, no -config necessary)


Entry 0:
  Name:                         `AAA-trii-CAI01'
  Organizational Unit:          `'
  Organization:                 `tribaldi'
  Locality:                     `'
  State:                        `'
  Country/region:               `'
  Config:                       `\AAA-trii-CAI01'
  Exchange Certificate:         `'
  Signature Certificate:        `'
  Description:                  `'
  Server:                       `'
  Authority:                    `AAA-trii-CAI01'
  Sanitized Name:               `AAA-trii-CAI01'
  Short Name:                   `AAA-trii-CAI01'
  Sanitized Short Name:         `AAA-trii-CAI01'
  Flags:                        `1'
  Web Enrollment Servers:       `'
CertUtil: -dump command completed successfully.

So then you copy the config value and use it like:

C:\system>certutil -catemplates -config "\AAA-trii-CAI01"
3b_clmAgent: 3b_clmAgent -- Auto-Enroll: Access is denied.
3B_enrollment_agent: 3B_enrollment_agent -- Auto-Enroll: Access is denied.
3BUser_onbehalf: 3BUser_onbehalf -- Auto-Enroll: Access is denied.
WebServerORG: Web Server ORG -- Auto-Enroll: Access is denied.
3bcomputer: 3bcomputer -- Auto-Enroll: Access is denied.
ISE_BYOD: ISE_BYOD -- Auto-Enroll: Access is denied.
BYOD: BYOD -- Auto-Enroll: Access is denied.
WebServer: Web Server -- Auto-Enroll: Access is denied.
IPSECIntermediateOffline: IPSec (Offline request) -- Auto-Enroll: Access is denied.
CEPEncryption: CEP Encryption -- Auto-Enroll: Access is denied.
EnrollmentAgentOffline: Exchange Enrollment Agent (Offline request) -- Auto-Enroll: Access is denied.
CertUtil: -CATemplates command completed successfully.

So while that is cool for scripting purpose, e.g. for documentation, the shortcut is much simpler. Instead of the value behind the config parameter you can just type a dash (minus sign), and that will open a dialog showing you all CA registered in AD. See here

And that will give you exact the same result as in the command before. 

Until next time,

P.S. these examples are created from Windows 2012 R2 machines.