Running tSQLt project from sources fails on the due to the Public Token mismatch

Question

I'm trying to do some improvements for tSQLt framework and I'm mostly finish, but can't do that as few tests related to the certificate I wasn't touching are still failing:

 [exec] |18|[InstallExternalAccessKeyTests].[test creates correct certificate in master]                                                       |    120|Failure|
 [exec] |19|[InstallExternalAccessKeyTests].[test tSQLt can be set to EXTERNAL ACCESS after InstallExternalAccessKey executed]                 |    333|Failure|
 [exec] |20|[InstallExternalAccessKeyTests].[test tSQLtExternalAccessKey install data is signed with same key as tSQLt.clr]                    |     10|Failure|

The failing error is:

Expected: %publickeytoken=8c9a92de0f9c7794,%
Actual: tsqltclr, version=1.0.7216.13228, culture=neutral, publickeytoken=8d3f4bf59e4c22fd, processorarchitecture=msil

I'll try to describe my installation process.

I installed this framework sources by using following guideline. However I couldn't start the project after that as it was failing with the following errors and the whole problem solving was done in that SO post:

[exec]
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Microsoft.Common.targets(2482,5): error MSB3325: Cannot import the following key file: tSQLtOfficialSigningKey.pfx. The key file may be password protected. To correct this, try to import the certificate again or manually install the certificate to the Strong Name CSP with the following key container name: VS_KEY_C20B79DE0583A5C1 [E:\Source\tSQLt\tSQLtCLR\tSQLtCLR\tSQLtCLR.csproj] [exec]
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Microsoft.Common.targets(2482,5): error MSB3321: Importing key file "tSQLtOfficialSigningKey.pfx" was canceled. [E:\Source\tSQLt\tSQLtCLR\tSQLtCLR\tSQLtCLR.csproj]

to pass this issue, I've created new keys for every project with no password:

I see that it created snk file, not as original pfx. After that I've tried to run the build again and it failed with the errors I mentioned above.

Why using `tsql` framework at all? It includes so many code in your database code, a lot of SQL CLR function. I have try to use if for 2 big projects over the year and it fails badly. It will be better to create own test using .net or/and SQL and execute them via some tool to get parallel execution. — gotqn, Nov 04 '19 at 07:09
I'm not really sure what exactly are you talking about. There are only several SQL CLR functions there. Additionally it is very stable and "fails badly" just sounds strange. Even Redgate included this framework to their products. You can google for it and you'll find that it is the best unit testing framework so far. — Dmitrij Kultasev, Nov 04 '19 at 13:54
I know it is the best, but it is not good. I have used it a lot of times and attend a few online demonstrations, requested enhancement and stuff, but I end up creating own tests. That's my advice - do not rely on this framework. — gotqn, Nov 04 '19 at 13:57
I use it for many years already and it always worked well for me, on the big projects also. — Dmitrij Kultasev, Nov 04 '19 at 14:00

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

As stated here

The following error may occur when building projects for Microsoft Visual Studio 2008 - 2015.

Error: Cannot import the following key file: mykey.pfx. The key file may be password protected.

Cannot import the following key file: mykey.pfx. The key file may be password protected. To correct this, try to import the certificate again or manually install the certificate to the Strong Name CSP with the following key container name: VS_KEY_C1D3ACB8FBF1AGK4

SOLUTION 1:

Click Start > All Programs > Microsoft Visual Studio >Visual Studio Tools > Visual Studio Command Prompt. Type the following command

sn -i "c:\Pathtofile\.pfx" VS_KEY_C1D3ACB8FBF1AGK4

Note: The sn.exe with the -i parameter, installs a key pair from into a key container named.Re-import the PFX file into Visual Studio.

SOLUTION 2:

Open Project Properties.
Click on the Signing section.
Where it says ‘Choose a strong name key file:’, re-select the current value from the drop-down box
Visual Studio will now prompt you for the password. Enter it. You might get another error message: ”An attempt was made to reference a token that does not exist” > You can simply ignore this message.
Click the ‘Change Password” button
Enter the original password in all three boxes and click OK. If you’d like to change
your password (or if your old password doesn’t meet complexity requirements), you can do so now.
Repeat for each key file in your project. Save your project and do a rebuild.

SOLUTION 3:

Get a copy of openssl for windows at slproweb.com or use a Linux box as they all pretty much have it. Run the following to export to a key file

openssl pkcs12 -in certfile.pfx -out backupcertfile.key

openssl pkcs12 -export -out certfiletosignwith.pfx -keysig -in backupcertfile.key

Then in the project properties you can use the PFX file

SOLUTION 4:

The final solution for this issue is to give up on Visual Studio for signing purpose and use Signtool instead.

Updated Answer

It seems that your TFS Build Service account has no required permission to access the signingKey.pfx on build agent machine. Make sure you have this file on build agent machine first.

Then follow below steps:

Log on the build agent as your local build service account (Better have Administrator permission)
Open a visual studio command prompt and navigate to the directory the key is stored in
Type command sn –i signingKey.pfx VS_KEY_EFCA4C5B6DFD4B4F(Ensure that you use the key name appearing in the error message)
When prompted for a password type the password for the pfx file
Then rebuild it

Note: If you are not running Visual Studio as an Administrator try doing that as well.

More details you can reference the answer from Brandon Manchester Cannot import the keyfile 'blah.pfx' - error 'The keyfile may be password protected'

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 01 '19 at 08:21

Vignesh Kumar A

27,863
13
63
115

I do not know password for a key – Dmitrij Kultasev Nov 02 '19 at 13:35
do you have any ideas? – Dmitrij Kultasev Nov 04 '19 at 05:35
@DmitrijKultasev You can simply open `Project` -> `Property` -> `Signing` and uncheck the "Sign the ClickOnce manifests" checkbox. – Vignesh Kumar A Nov 04 '19 at 06:04
@DmitrijKultasev Updated answer too. check that as well – Vignesh Kumar A Nov 04 '19 at 06:08
1

All provided solutions in your case required password. I do not have one. `Sign the ClickOnce manifests` is unchecked and DISABLED. I'm trying to do everything locally, so there is no permissions issues (I've tried to run this command line under the administrator) – Dmitrij Kultasev Nov 04 '19 at 13:59
Kumar, any other ideas? – Dmitrij Kultasev Nov 05 '19 at 10:17
@DmitrijKultasev which version you are using? – Vignesh Kumar A Nov 05 '19 at 10:20
If you are talking about Visual Studio, then it is 2017 – Dmitrij Kultasev Nov 05 '19 at 10:26
@DmitrijKultasev http://www.slickit.ca/2010/09/fix-cannot-import-following-key-file.html Can you try this – Vignesh Kumar A Nov 05 '19 at 10:30
1

There 7 point that says "Enter the original password in all three boxes and click OK. If you’d like to change your password (or if your old password doesn’t meet complexity requirements), you can do so now. ". As I stated already, that I do not have password. – Dmitrij Kultasev Nov 05 '19 at 10:37
@DmitrijKultasev you must enable `Sign the ClickOnce manifests` let me see why it was disabled – Vignesh Kumar A Nov 05 '19 at 10:56
It is greyed out, I can't select it. Maybe DB projects do not support that.. – Dmitrij Kultasev Nov 05 '19 at 10:58
@DmitrijKultasev just right click on the pfx file from VS Solution Explorer and select “Open Command Prompt”. A prompt opens right at the folder containing the key file, and I enter the command: `n -i magellanicKey.pfx tSQLtOfficialSigningKey` – Vignesh Kumar A Nov 05 '19 at 11:10
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/201875/discussion-between-dmitrij-kultasev-and-vignesh-kumar-a). – Dmitrij Kultasev Nov 05 '19 at 11:14
I've answered in chat, that I do not have such option – Dmitrij Kultasev Nov 05 '19 at 11:17
refer this, you must have https://jeremylindsayni.wordpress.com/2015/01/22/strongly-named-assemblies-installing-a-pfx-to-your-machine/ – Vignesh Kumar A Nov 05 '19 at 11:26
1

in that example author knows the password and I don't – Dmitrij Kultasev Nov 05 '19 at 11:36
You can enter password for that section – Vignesh Kumar A Nov 05 '19 at 12:15
Failed to parse the PKCS#12 blob in tSQLtOfficialSigningKey.pfx -- The specified network password is not correct. – Dmitrij Kultasev Nov 05 '19 at 13:43

Running tSQLt project from sources fails on the due to the Public Token mismatch

1 Answers1