Questions tagged [signature]
A means of conveying the authenticity of a message, file, or code module. A public and private key pair (sometimes placed into a certificate) is used in this process. The private key is used to generate the signature / sign the content. The public key is then used to verify the content against the signature. In Microsoft SQL Server, signatures can be added to stored procedures, functions, triggers, and assemblies.
19
questions
0
votes
0
answers
58
views
External verification of `SIGNBYCERT()` signatures from SQL Server 2016
I want to sign plaintext using a SQL Server certificate and then verify that signature using the certificate's public key on an external system. Here's what I've tried.
-- Create a self-signed ...
- 765
0
votes
0
answers
166
views
SQL Server - How to know to which database user certificate is mapped to?
Inside a user database, I have two certificates, both are used for signing stored procedures
First cert was created long time ago by someone
Second I created myself for testing purposes
For ...
- 5,182
5
votes
1
answer
4k
views
Added .Net assembly to SQL CLR without turning TRUSTWORTHY ON
I'm trying to add "System.Messaging.dll" using the guidance provided by Solomon Rutzky in his post Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key but I'm failing at ...
- 53
2
votes
0
answers
216
views
SignByCert private key password
So, given MS SQL 2008R2 or better:
I want to not only store data in a table but be able for my application to automatically verify it was not modified.
Solution seems to be to use SignByCert to ...
4
votes
2
answers
98
views
Why can I not DENY permissions through module signing?
In the documentation for ADD SIGNATURE, they have the following caveat:
Module signing should only be used to grant permissions, never to deny or revoke permissions.
My question is: why? ...
- 1,850
5
votes
2
answers
2k
views
Can't use msdb.dbo.sp_send_dbmail when in service broker - executes as guest?
I have a procedure TheNotificationProcedure that makes a cross-database call to msdb.dbo.sp_send_dbmail.
It gets invoked (indirectly) from a service broker queue:
CREATE QUEUE [Blah].[TheQueue]
WITH ...
- 151
12
votes
1
answer
3k
views
Permissions in triggers when using cross database certificates
I use cross database certificates (as explained by Erland Sommarskog) to control access to a certain database in my environment ( SQL Server 2008 R2 ).
I have stored procedures in database A that ...
- 294
1
vote
1
answer
3k
views
SQL Server Impersonation is just NOT working
I simply would like a very limited user, in its own database, to call an update procedure on our database to tell us that it has updated a table, which it has access to.
I have allowed the user ...
- 133
4
votes
1
answer
2k
views
Impersonate user or login mapped to certificate
Assume that there is a certificate created in database
create certificate certName
with subject = 'subj';
GO
And a user mapped to this certificate
create user userName
from certificate ...
- 2,334
6
votes
1
answer
6k
views
Stored procedure security with execute as, cross database queries, and module signing
I have a situation that, while I was able to work around it (as the repro will show), I don't understand. Here are the high points
Two databases, ChainingSource and ChainDestination, both of which ...
- 1,850
9
votes
2
answers
16k
views
Error: "The certificate cannot be dropped because one or more entities are either signed or encrypted using it."
I have a certificate I would like to remove from the database.
If I issue the command
DROP CERTIFICATE <FooCert>
I get the error
The certificate cannot be dropped because one or more entities ...
- 1,083
5
votes
1
answer
4k
views
Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys
I have created a few Certificates (via CREATE CERTIFICATE) and Asymmetric Keys (via CREATE ASYMMETRIC KEY) and used them to sign and counter-sign various Stored Procedures, User-Defined Functions (...
- 67.7k
3
votes
1
answer
1k
views
SSDT Schema Comparison for Stored Procedure signed by a Certificate
I am using SSDT 14.0.50730.0 in Visual Studio Professional 2015. I am comparing databases between two instances and it is finding that there are stored procedures that do not match with regard to ...
- 2,140
4
votes
2
answers
1k
views
Assigning permissions for sysmail_update_account_sp
I have a Microsoft SQL Server 2014. The mail system is configured via DBMail and
there is one stored proc in main web app (having its own database) which executes msdb's sysmail_update_account_sp by ...
- 153
3
votes
2
answers
3k
views
DDL trigger permission Error
I have a two users, user1 and user2.
User1 has sysadmin rights and I have created a database level trigger through sa.
Now I am trying to create a table through user2 who has db-ddladmin rights and ...
- 53
6
votes
1
answer
687
views
Is it possible to add a certificate to a database role?
I have a certificate I am using for inter-DB access to a stored procedure in SQL Server, and am able to provide access to the stored procedure by creating a new user in the database using:
CREATE ...
2
votes
2
answers
4k
views
Grant execute on xp_logevent to sa using sa user to do it
Running a SQL Server 2012 CLR stored procedure with this code:
const string executeLog = "EXEC master..xp_logevent @errorNumber, @message, informational";
using (SqlCommand cmd = new SqlCommand(...
- 1,823
2
votes
1
answer
1k
views
SQL Server Chain Permission
We have the 2 databases.
Reporting
HR
There are some users who want to extract data from HR database. But we don't want to give direct access to that HR database.
So, we created the special views ...
- 123
7
votes
3
answers
9k
views
Sharing certificates encryped by password between DBs and instances
Question:
I am trying to create a single (self signed) certificate and deploy across many databases and instances.
I create and backup the cert with:
USE MASTER
CREATE CERTIFICATE DavesCert ...
- 201