SQL Security :: Importing Existing PEM Certificate Into Server
Oct 26, 2015
I must prepare a procedure which should export some data from some DB columns, but these data must be encrypted using the OpenSSL protocol, to be sent to an external recipient.This recipient sent to me a "public" certificate in PEM format, to what I understood (it is a file beginning with the expression "-----BEGIN CERTIFICATE-----", then a long Base64 sequence and then "-----END CERTIFICATE-----").
Then I didn't create the certificate nor I am a certification authority: I just have this public key (or, at least, a file from which I can extract a public key) and I must use it to encrypt data in order to obtain an output which must be analogue to what I can obtain when using this OpenSSL command line instruction:openssl rsautl -encrypt -in CF.txt -out CF.enc -inkey SaniteICF.cer -certin -pkcs
Then the question is: starting from this PEM file and nothing else, can I import it and store an asymmetric key or a certificate in SQL Server and use it to encrypt data to be exported?
How do I install a Microsoft SQL Server 2005 database application in an existing server in an existing database server and still have the control over it and also restrict the new server Admin user from editing or even opening my DB. Any modification of my database or code should be implemented only by me. Is it possible to remove the Builtin Admin from the server role?? As in my case, there is no need for anyone else to open the DB in Management Studio at all as my VB application does all that is required.
Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.
Following script runs fine on SQL Server.
Code Snippet
use master
Create Master Key Encryption BY Password = '45Gme*3^&fwu';
BACKUP MASTER KEY TO FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk'
ENCRYPTION BY PASSWORD = '45Gme*3^&fwu'
Create Certificate EndPointCertificateC
WITH Subject = 'C.Server.Local',
START_DATE = '06/01/2006',
EXPIRY_DATE = '01/01/2008'
ACTIVE FOR BEGIN_DIALOG = ON;
BACKUP CERTIFICATE EndPointCertificateC
TO FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
Following script runs on SQL Server Express:
Code Snippet
Create Certificate EndPointCertificateC
From FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
WITH PRIVATE KEY (
FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk',
DECRYPTION BY PASSWORD = '45Gme*3^&fwu'
);
If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.
I've been reseaching on SSB and have read quite a number of posts on this forum that closely relate to what im trying to achieve. I have a solution im designing that ideally consists of a central server (SQLENTERPRISE) that will receive messages asynchronously from remote clients (SQLEXPRESS) spanning a wide geographical region over a GPRS virtual private network on a TCP/IP transport. This ideally is a star and spoke architecture and requirements dictate high level security, no loss of messages whatsover as well as high reliability and scalability.
To meet the security requirement in the context of the above scenario, i was thinking implementing both dialog and endpoint security using certificates would be ideal. I've downloaded some samples and have encountered problems simulating the above scenario on 3 machines (I Server & 2 Clients). Client1 sends messags successfully, the 2nd client doesnt possibly because the certificate on the server matches that to client1 since i ran that script first. How do i make it that the Server shares one certificate with all remote clients? Or is there a better way to configure SSB to work in this scenario?
Secondly i have hard coded the Server's IP Address in the Routes created on the Clients. Considering this is over a GPRS Virtual network, how can i make the clients dyamically "discover" the Server? The idea here is to make adding and setting up of new remote clients easy so that you can just plug them in to the existing network
Any help or pointers would be greatly appreciated.
I want to use an Active Directory security group that is a Distribution List for a new role assignment for an existing report. Can someone tell me if this is possible? I get an error each time I try:
The user or group name <DLName> is not recognized. (rsUnknownUserName)"
i m trying to send message between different server instance using service broker.
and for security purpose i am trying to create certificate. for that i have used makecert.exe and get a certificate and a private key. but when i am creating certificate using that file it is showing error
the code is --
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:SourceServer.cer'
WITH PRIVATE KEY ( FILE = 'C:SourceServer.pvk', DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO
i have created the file SourceServer.cer' and SourceServer.pvk' by using makecert.exe tool.
the idea behind creating the certificate ctfSourceServerMaster is to give transport security.
I am running the particular script in the master database.
but still i am getting error
ERROR:----
The certificate, asymmetric key, or private key file does not exist or has invalid format.
We are unable to login in database due to “The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x80090331. Check certificates to make sure they are valid. Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.”we have tried to run that selfssl.exe from command prompt followed by below command and am getting the cryptographic error.
There doesn't seem to be a specific group covering legacy subsystems so I thought I would post this in here.
I have several hundred of these to import that are going to be left to run as they are until such time somebody has the time to migrate them to SSIS.
I have to say I'm not look forward to doing them one at a time using the brain dead UI (it folds up folders behind itself causing another two mouse click between each import). Is there a way of automating this import?
Good Morning, I need some assistance with SQL Server 2000 Importing Data. When I import data from a text on a routine basis, three things must happen: 1. New records identified by primary key get appended to table. 2. Exisiting records identified by primary key get overwritten with new/(updated) data. 3. All other existing records are left alone. Does anyone know how to Import Records with the following the criteria above? It cannot insert duplicate primary keys by nature, so it must overwrite those records! This is being built into a DTS Package, but I need to get over this obsticle! Thanks for any guidance!
I have an existing table I need to add data to. The data is in a text file, and the existing table already has data in it (I don't want to delete this I want to add to it).
I used Microsoft's import utility but this created a seperate table with generic fieldnames (column01, column02, ect). Is there a step in this wizard I missed?
I have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
I wish to do a major change on our security profile. Basically, I wish to allow a group of users to do everything but modify objects/change logins/drop-create objects. Is there a quick way to build and apply this model ?
I want to load data that i receive everydays from my customers in .xls file format (excel) or cvs file format, to the database that i have created on this purpose. but when trying to do that whith SSIS; i got an error message .... that i can't import redudant data in my database column.
Im experimenting with creating Certificates for SQL Server. I can create a certificate ok, using the Certification Authority Wizard, however when I go into SQL Server Configuration Manager and view Protocols for MSSQLServer/Properties and click on the drop down list for Certificate theres nothing there. When I create the certificate using the Wizard, Im only given the following stores to use: Personal, Trusted Root Certification Authorities, Enterprise Trust, Intermediate Certification Authorities, Trusted Publishers, Untrusted Certificates, Third-Party Root Certification Authorities and Trusted People. Does anyone know where I should be Importing the certificate to, or have any tips on what I need to do to view the certificate there?
Hi all, I created a certificate in the master database so that my endpoint could use the certificate.
I dropped the endpoint and dropped the certificate and added a new one and created a new endpoint because the earlier one had a weak password.
Everything has been fine till we restarted the server. Master, model start up fine but now I am getting these errors.
Starting up database 'master'. 2007-09-25 10:40:53.62 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required. 2007-09-25 10:40:53.77 spid5s CHECKDB for database 'master' finished without errors on 2007-02-04 01:00:14.597 (local time). This is an informational message only; no user action is required. 2007-09-25 10:40:53.90 spid5s SQL Trace ID 1 was started by login "sa". 2007-09-25 10:40:53.95 spid5s Starting up database 'mssqlsystemresource'. 2007-09-25 10:40:54.24 spid5s Server name is 'Billing2348'. This is an informational message only. No user action is required. 2007-09-25 10:40:54.24 spid9s Starting up database 'model'. 2007-09-25 10:40:54.40 Server Error: 17190, Severity: 16, State: 1. 2007-09-25 10:40:54.40 Server FallBack certificate initialization failed with error code: 1. 2007-09-25 10:40:54.40 Server Warning:Encryption is not available, could not find a valid certificate to load. 2007-09-25 10:40:54.40 Server Error: 17182, Severity: 16, State: 1. 2007-09-25 10:40:54.40 Server TDSSNIClient initialization failed with error 0x7e, status code 0x60. 2007-09-25 10:40:54.40 Server Error: 17182, Severity: 16, State: 1. 2007-09-25 10:40:54.40 Server TDSSNIClient initialization failed with error 0x7e, status code 0x1. 2007-09-25 10:40:54.40 Server Error: 17826, Severity: 18, State: 3. 2007-09-25 10:40:54.40 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log. 2007-09-25 10:40:54.40 Server Error: 17120, Severity: 16, State: 1. 2007-09-25 10:40:54.40 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
I even copied the master database to a dev server and started it up deleted all certificates and endpoints but it gives the same error.
Are there limitations to express and certificates, i can create certificates in express. But when i try to import a certificate from another server (developer edition) into my express edition i get his error:
The certificate, asymmetric key, or private key file does not exist or has invalid format
When i use the same certificate backup on non express editions it works 100% , is it not possible or must it be done differently ?
for the third time I am installing SQL-Server 2005 express and i have an error.
Cannot install sql because .NET 2.0 framework is missing allthough I have the .NET 2.0 framework allready installed and even re-installed it too. When trying again I get the message that there isn't an SSL-certificate present.
My question is, would this probably cause the error when installing?
I was trying to install Sql Server Express 2005 using the setup made using Visual Studio 2005 Publisher Wizard, as a pre-requisite. During this i'm getting the following error...
"SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted."
What does this mean? Anyone is familiar with this, then pray please post a reply. I'm in the middle of nowhere now....
How do we install a certificate for enabling security on MS -SQL Server 2005? I tried to install through MMC but it says no " certificate authority found " . Any help will be highly appreciated , Thanks to you all in advance :-)
I want to use a certificate with 10-years expiration date. I created a new template certificate, based on default computer certificate, and I only change expiration date to 10 years.
In SQL Server SSL configuration, I dont view this certificate. In only view certificate created with the "Computer" template (with validity period of 365 days). All others certificates based on template are invisible to SQL Server
How can I use a custom template instead of the default "computer" template ?
Hi I am trying to install SQLEXPRESS 2005 and i get this error: SQL Server Setup faild to retrieve the SSL Certificate Name and an OK button and when i hit the button, the installiatoin stops. The Event log shows this: Product: Microsoft SQL Server 2005 -- Error 1603. SQL Server Setup failed to retrieve the SSL Certificate Name.
Hi I am trying to install SQL Server 2005 Express on my machine, which has Windows Server 2003 with Service Pack 1. I could not able to install properly and i am getting error always. Here is the Error I am getting always "The certificate chain was issued by an authority that is not trusted" . I am trying for past few days i could not able to resolve.
Please help Thanks Here is the Error message from Summary.txt
Microsoft SQL Server 2005 9.00.1399.06 ============================== OS Version : Microsoft Windows Server 2003 family, Service Pack 1 (Build 3790) Time : Tue Sep 05 12:31:18 2006
-------------------------------------------------------------------------------- Machine : SERVIDORCC Product : Microsoft SQL Server 2005 Express Edition Product Version : 9.00.1399.06 Install : Failed Log File : C:Archivos de programaMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0008_SERVIDORCC_SQL.log Last Action : InstallFinalize Error String : SQL Server Setup could not connect to the database service for server configuration. The error was: {Microsoft}{SQL Native Client}SSL Provider: The certificate chain was issued by an authority that is not trusted. Error Number : 29515 --------------------------------------------------------------------------------
SQL Server Setup failed. For more information, review the Setup log file in %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt. Time : Tue Sep 05 17:12:41 2006
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
I attempted to setup database mirroring using a High Availability scenario but when I installed SQL is chose to use local system accounts for all the services. Consequently, I stubled upon a microsoft article explaining how to setup mirroring using local system accounts and certificate authentication but I am stil not able to get it to work. When I try ti initiate the mirror from the mirror server I receive an error stating "Neither the partner nor the witness server instance for database "EDENLive" is available. Reissue the command when at least one of the instances becomes available." I have checked all the endpoints and everything seems to be in order. I even checked to make sure that each server was listening on the appropriate ports and I AM able to telnet to the ports. Please help!
I would like to restore database using RESTORE DATABASE ... REPLACE command. If database exists already and has any open connections this command will fail. I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command. I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
Can anybody tell me the advantage and disadvantage to use NT security for SQL Server 7.0? For a corporation with 400 users, what is your recommendation for the SQL Server security management. Thanks.
