Blog

Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission.

Recently we had an issue with one of our customers who was not able to login into her SQL Server. In SQL’s errorlog we found this message:

At first we checked the permissions, they seemed to be ok.

So we went back to the original message in the log. To be honest, we didn’t read the message well enough, and were a little bit confused about the first part of the message: “Token-based server access validation failed with an infrastructure error.” Since the database permissions were fine, we thought there had to be an issue with the AD account itself, since we were able to connect without any issue.

Unfortunately, that was not the case either. So we had to dig a little deeper. Now we took the time to read the second part the error message in the error log (“Login lacks Connect SQL permission”), which is, to be totally honest quite clear.

It turned out our customer was member of a domain group called Marketing. We used this query to check any instance level securables on the group:

And look, there it was… The AD group our customer is part of, has no permission to connect to the SQL Server.

Conclusion

Remember, DENY overrules all other permissions. The customer could not connect because of one of her groups was denied access, even though her permissions were just fine. So we granted the correct permissions to the group, removed the customer’s individual login and after that, she was able to connect again.

Powershell Gallery and proxy servers

When your pc is using a proxy server to connect to the internet, the following needs to be added to your Powershell profile. Otherwise, you won’t be able to register any Powershell repository. This means you cannot use the commandlet Install-Module to install modules.

Open an elevated Powershell prompt and type:

This will create a new profile when not available, and opens a Notepad for you. Paste the following into your profile and save the file:

Now close your Powershell console, and start a new elevated session. Now it should be possible to register the default repository and you can install any module using Install-Module:

Startup type of Windows Service

To retreive the startup type of a Windows Service, use this:

When using Powershell version 4 and earlier, the property StartType is not populated, so you need to query Wmi:

To change the startup type to Automatic:

Sql Engine and Sql Agent Service Account Information

Dbachecks: How to add a custom repository

Sometimes you want to check something in your SQL environment that’s not yet included in the dbachecks module. Luckily, it is quite easy to extend it’s functionality.

Let’s start by creating a file that will contain your own checks:

Now add the following to the newly created file:

In the Describe block I added ‘Contoso’ to the Tags parameter. You can use this tag to include (or exclude) specific tests.

All that’s left to do is to register your custom repository:

Now you can invoke the checks by running:

Import Excel files into SQL Server

Recently I was given a nice task: can you import some Excel files into a SQL Server database. Luckily, using dbatools and ImportExcel this was an easy task. The files were named <schemaName>.<tableName>.xlsx. I made sure all needed schemas were available in my database, and then I used the following script to import the files.