How to use asymmetric encryption when only public key is known

Question

How to use asymmetric encryption when only public key is known

Arnoud Commandeur 1

I would like to use a blockchain public address to encrypt data inside a SQL table column.

I wonder how to use the asymmetric encryption when I only know the public key of the reciever?

EchoLiu-MSFT 14,621 Reputation points

2020-12-25T09:07:59.777+00:00

Do you have any updates？
Please also remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

3 answers

Your answer

EchoLiu-MSFT 14,621 Reputation points

2020-12-25T09:07:59.777+00:00

Do you have any updates？
Please also remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

Answer 1

Guoxiong 8,206

If you know the public key (asymmetric key), you can use the function ENCRYPTBYASYMKEY(ASYMKEY_ID('{KEY_NAME}', [Column_Name]) to encrypt data.

Arnoud Commandeur 1 Reputation point

2020-12-19T06:30:53.93+00:00

The problem with this solution is that first a new key asymmetric key inside the database has to be created which will create a new private and public key. I only have the public key of te reciever.

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

You can import a public key with CREATE ASYMMETRIC KEY FROM FILE. This reduces the problem how get the public key to a file which SQL Server can read. Which itself may be trivial - or something which is very difficult to overcome, depending on the situation. It would have been nice if you could create key from a binary string, but that option is only available for certificates.

I should add that the topic for CREATE ASYMMETRIC KEY in Books Online says Specifies the source from which to load the asymmetric key pair, but it works with a public key only. I tested.

Arnoud Commandeur 1 Reputation point

2021-02-06T12:19:15.057+00:00

Could you add an example how you imported only a public key?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-02-06T12:58:32.257+00:00
CREATE ASYMMETRIC KEY keyname FROM FILE 'c: \temp\mypublickey.key'

The same syntax as for importing a key pair, except that the ENCRYPTION BY clause is not needed.

The file only includes the public key.
Arnoud Commandeur 1 Reputation point

2021-02-06T13:00:33.627+00:00

my problem is that I have no idea how to create the mypublickey.key file on a sql server which has only the public key of the end-user. any help is welcome.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-02-06T13:09:16.15+00:00

Note sure that I see the relevance to SQL Server here. SQL Server can read a key from a file. How you create that file is not really an SQL Server topic. You talk about a "blockchain public address", which I don't even know that it is. You may have to find another forum where they may know ´what that is, and how you get it to a file.
Arnoud Commandeur 1 Reputation point

2021-02-06T13:15:20.13+00:00

I will explain a little more what I try to: client sends a public key to the sql server. The server uses that public key to encrypt a message and sends that message back. Now it lokos like I have to create a certificate first by using another tool. Is that correct?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-02-06T14:14:46.627+00:00

No, you don't need to create a certificate. Who said that?

You say "client sends a public key to the sql server"
a) What is "client" here? Someone paying your bill? Or "client" as in application?
b) What do you mean with "sends ... to .. sql server"?

Answer 3

Hi @Arnoud Commandeur ,

Other experts have provided some methods, please also refer to the following article:
Using Asymmetric Encryption and Digital Signatures in a SQL Server 2005 Database

Regards
Echo

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Share via

How to use asymmetric encryption when only public key is known

3 answers

Your answer