SQL Security :: Unable To Initialize SSL Encryption Because A Valid Certificate Could Not Be Found
Nov 19, 2015
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.
I'm having this really strange issue with mirroring.
I've followed the instructions on http://msdn2.microsoft.com/en-us/library/ms191140.aspx to the letter, leaving out the witness server part, as I don't have one.
All works OK, but when activating the mirroring on the principal server I get the usual error 1418 error. I fired up SQL Server Profiler to see what was happening, and the following error emerged.
Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not yet valid. State 104.
I am trying to create a encrypted row in my database Everything here worked except that when i run the final query to decrypt the data It just comes up with null for each row. Even if i do a query to show me the rows that are not null It's like it is saying yeah there is data here but I am only going to show you null instead of what I am supposed to decrypt.Here is what I tried from start to finish Create Certificate TestCert Encryption By Password = 'Password' With Subject = 'SQLCert', Expiry_Date = '12/01/2050';
declare @Test nvarchar(50) set @Test='123456789'
insert into testenc (testencry) Values (encryptbyCert(Cert_ID('TestCert'),@Test ))
select convert (Nvarchar(50), DecryptByCert(Cert_ID('TestCert'), testencry,N'Password')) As Test from testenc
What I wont is to be abele to do is to send a message from Sender? to Receiver 1? or Recevier 2?.
I am abele to send a message from Sender? to Receiver 1? but if I send a message to Receiver 2? I get a dialog security problem I think. If I use profiler I can se in Receiver 2? the events:
Broker:Connection Audit Broker Login Broker:Message Classify Audit Broker Conversation = Certificate not found Broker:Message Undeliverable
And I cant find whats wrong, this Is my scripts for etch instance.
Sender?
USE master
CREATE CERTIFICATE Cert_ROBOTSRV WITH SUBJECT = 'Cert_ROBOTSRV_auth', START_DATE = '02/15/2007', EXPIRY_DATE = '02/15/2015' GO BACKUP CERTIFICATE Cert_ROBOTSRV TO FILE = 'C:Cert_ROBOTSRV'
GO
CREATE ENDPOINT SBEndpointServer STATE = STARTED AS TCP (LISTENER_PORT = 5723) FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_ROBOTSRV)
GO CREATE USER andon
CREATE CERTIFICATE Cert_sevapc311_pub AUTHORIZATION andon FROM FILE = 'C:Cert_sevapc311'
GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV] TO andon
CREATE USER sbLogin
GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV] TO sbLogin GO --GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV] --TO [VADERSTADvrobot] --GO
-- Grant RECEIVE permission on the queue. GRANT RECEIVE ON [Grafik_Queue] TO andon GO GRANT CONTROL ON SERVICE::[Grafik_Service_Send_ROBOTSRV] TO andon GO
--DROP REMOTE SERVICE BINDING Grafik_sevap CREATE REMOTE SERVICE BINDING Grafik_sevap TO SERVICE 'Grafik_Service_Recive_sevapc311' WITH USER = andon GO
--DROP REMOTE SERVICE BINDING Grafik_andonpc017 CREATE REMOTE SERVICE BINDING Grafik_andonpc017 TO SERVICE 'Grafik_Service_Recive_Andonpc017' WITH USER = andon
Receiver 1?
use master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'
I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.
best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.
SELECT ID FROM table WITH (NOLOCK) WHERE dbo.DecodeFunction(column) = 'value'
When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.
I set up Service broker (2008R2 -> 2012) across different servers and domains using certificates.I set up 2 queues (one for sending, one for receiving).I set up 6 services (3 for send, 3 for receive), all 3 sharing the same queue type. (srv_send_1, srv_send_2, srv_send_3), (srv_receive_1, srv_receive_2, srv_receive_3).I set up a route for each receiving service on the source, and for each sending service on the target.I set up a SB binding to the remote for each sender service type.I granted send rights to the sending services on the source server.
I then tested my first service, and it worked perfectly (still works).My second service however is failing with the error "The certificate's private key cannot be found".I am stumped, as all the setup code was a lot of copy/paste which I have checked, rechecked and even re-coded.Basically, here is what Im doing:
begin dialog @ConversationHandle FROM Service srv_send_1 TO Service 'srv_receive_1' ON CONTRACT myContract WITH ENCRYPTION = OFF, LIFETIME = 60*60*24*7; SEND ON CONVERSATION @conversationHandle MESSAGE TYPE [my_msgtype] (@xml);
The above code works 100% with service1, but not with service2.Note that on profiler, the target server doesnt receive any communication. The error shows on the sender profiler.Also, The conversation shows up as CONVERSING in sys.conversation_endpoints and the entry exists in sys.transmission_queue.
I am trying to test load balancing between multiple broker service instances. I have set up one sender and two receivers. When I tried sending a lot of messages from the sender, I noticed that all messages were being received by receiver 1 alone. While I am able to communicate between sender and receiver 1, I am not able to send message to the second receiver (I stopped the first receiver instance to find this out). I receive the "certificate not found" error in the Profiler for the second receiver. The code for my second receiver is very similar to the first one.
I am dumping in the full code down here. I appreciate if someone can figure out what is wrong. Thanks
Sender:
use [master]; go
create master key encryption by password = 'masterhello1'; go
create certificate TrpCertServ1 with subject = 'TrpCertServer1', start_date = '06/01/2005'; go
--make sure the cert exist select * from sys.certificates; go
--dump out the public key of the cert to a file --this will then be exchanged with the other instance --make sure that the path you define below can be accessed --by sql server. The file needs to be copied over to server 2 BACKUP CERTIFICATE TrpCertServ1 TO FILE = 'c:amitOfficialService BrokercertsTrpCertServ1Pub.cer'; go
--you need to create an endpoint in order to enable communication --outside of this instance CREATE ENDPOINT SSB1 STATE = STARTED AS TCP ( LISTENER_PORT = 4021 ) FOR SERVICE_BROKER ( AUTHENTICATION = CERTIFICATE TrpCertServ1, ENCRYPTION = REQUIRED ); go
USE master ; GO
ALTER ENDPOINT SSB1 FOR SERVICE_BROKER ( MESSAGE_FORWARDING = ENABLED, MESSAGE_FORWARD_SIZE = 10 ) ; GO
--check that the endpoint has been created select * from sys.endpoints; go
--create a login and a user which you eventually will assign a public --key from the cert in the remote master db to create login remconnlogin1 with password = 'remserver@1'; go
create user remconnuser1 from login remconnlogin1; go
--grant connect to the endpoint to the login grant connect on endpoint::ssb1 to remconnlogin1 go
--now is time to go over to server 2 and do similar tasks --but first make sure that the cert you dumped out above can be --available for server 2
--Step 2 -- you have now done the similar setup in server 2, --and you should now do the final setup in master --where you create a certificate from server 2's public --cert and assigns it to the user created above --uncomment from here to go and execute create certificate TrpCertServ2Pub authorization remconnuser1 from file = 'C:amitOfficialService BrokercertsTrpCertServ2Pub.cer'; go
--adding for new receiver create certificate TrpCertServ3Pub authorization remconnuser1 from file = 'C:amitOfficialService BrokercertsTrpCertServ3Pub.cer'; go -- end of addition
use master; go
--create the database create database [rem_ssb1]; go
create certificate DlgCertServ1Db1 with subject = 'DlgCertServ1Db1', start_date = '06/01/2005' active for begin_dialog = on; go
--make sure the cert exist select * from sys.certificates; go
--dump out the public key of the cert to a file --this will then be exchanged with the other instance --make sure that the path you define below can be accessed --by sql server. The file needs to be copied over to server 2 BACKUP CERTIFICATE DlgCertServ1Db1 TO FILE = 'c:amitOfficialService BrokercertsDlgCertServ1Db1Pub.cer'; go
--create a user which you eventually will assign a public --key from the cert in the remote db to create user remdlguser1 without login; go
--Step 2 create certificate DlgCertServ2Db2Pub authorization remdlguser1 from file = 'c:amitOfficialService BrokercertsDlgCertServ2Db2Pub.cer'; go
-- adding content for new receiver create certificate DlgCertServ3Db3Pub authorization remdlguser1 from file = 'c:amitOfficialService BrokercertsDlgCertServ3Db3Pub.cer'; go -- end of addition
use [rem_ssb1]; go
-- we need two message types CREATE MESSAGE TYPE [sendmsg] VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [recmsg] VALIDATION = WELL_FORMED_XML; go
--create the message contract --and define who sends what CREATE CONTRACT [Ctract] ( [sendmsg] sent by initiator, [recmsg] sent by target ); go
--create the queue, at this stage we do not care --about activation CREATE QUEUE q1 with status = ON; go
--we need a service CREATE SERVICE [rem_s1] on queue q1 ( [Ctract] ); go
--create a route to the remote service, we know it'll be called rem_s2 create route [rem_s2_route] with service_name = 'rem_s2', address = 'TCP://127.0.0.1:4022'; go --drop route [rem_s2_route]
-- adding for new route create route [rem_s3_route] with service_name = 'rem_s2', address = 'TCP://127.0.0.1:4023'; go -- end of addition
select * from sys.routes
--as we'll be doing encrypted dialogs we need a remote service bindin CREATE REMOTE SERVICE BINDING [myRms] TO SERVICE 'rem_s2' WITH USER = remdlguser1, ANONYMOUS=Off
--give the user send rights on the service grant send on service::rem_s1 to remdlguser1; go use [rem_ssb1]; go
----start the dialog and send a message ----uncomment from here until the following go statement and run DECLARE @h uniqueidentifier --conversation handle DECLARE @msg xml; --will hold the message
BEGIN DIALOG CONVERSATION @h FROM SERVICE rem_s1 TO SERVICE 'rem_s2' ON CONTRACT [Ctract];
SET @msg = '<hello00/>'; SEND ON CONVERSATION @h MESSAGE TYPE [sendmsg] (@msg);
RECEIVER 1:
--Step 1
use [master]; go
create master key encryption by password = 'masterhello2'; go
create certificate TrpCertServ2 with subject = 'Transport Certificate for Server2', start_date = '06/01/2005'; go
--make sure the cert exist select * from sys.certificates; go
--dump out the public key of the cert to a file --this will then be exchanged with the other instance --make sure that the path you define below can be accessed --by sql server. The file needs to be copied over to server 1 BACKUP CERTIFICATE TrpCertServ2 TO FILE = 'c:amitOfficialService Brokercerts2TrpCertServ2Pub.cer'; go
--you need to create an endpoint in order to enable communication --outside of this instance
CREATE ENDPOINT SSB2 STATE = STARTED AS TCP ( LISTENER_PORT = 4022 ) FOR SERVICE_BROKER ( AUTHENTICATION = CERTIFICATE TrpCertServ2, ENCRYPTION = REQUIRED ); go
--check that the endpoint has been created select * from sys.endpoints; go
--create a login and a user which you eventually will assign a public --key from the cert in the remote master db to create login remconnlogin2 with password = 'pass1234$'; go
create user remconnuser2 from login remconnlogin2; go
--grant connect on the endpoint to the login grant connect on endpoint::SSB2 to remconnlogin2 go
--copy in the public cert from server 1 to somewhere on this server
--create a certificate from the public cert from server 1 create certificate TrpCertServ1Pub authorization remconnuser2 from file = 'c:amitOfficialService Brokercerts2TrpCertServ1Pub.cer';
--go back to server 1 and step 2 in the script 1_setup_sec_master_server1.sql --make sure the public cert 'TrpCertServ2Pub.cer' is available --from server 1
use master; go
--create the database create database [rem_ssb2]; go
use [rem_ssb2]; go
--set master key create master key encryption by password = 'hellodb2'; go
create certificate DlgCertServ2Db2 with subject = 'DlgCertServ2Db2', start_date = '06/01/2005' active for begin_dialog = on; go
--make sure the cert exist select * from sys.certificates; go
BACKUP CERTIFICATE DlgCertServ2Db2 TO FILE = 'c:amitOfficialService Brokercerts2DlgCertServ2Db2Pub.cer'; go
create user remdlguser2 without login; go
--copy in the public cert from server 1 to somewhere on this server
--create a certificate from the public cert from server 1 create certificate DlgCertServ1Db1Pub authorization remdlguser2 from file = 'c:amitOfficialService Brokercerts2DlgCertServ1Db1Pub.cer'; use [rem_ssb2]; go
-- we need two message types CREATE MESSAGE TYPE [sendmsg] VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [recmsg] VALIDATION = WELL_FORMED_XML; go
--create the message contract --and define who sends what CREATE CONTRACT [Ctract] ( [sendmsg] sent by initiator, [recmsg] sent by target ); go
--create the queue, at this stage we do not care --about activation CREATE QUEUE q2 with status = ON; go
--we need a service CREATE SERVICE [rem_s2] on queue q2 ( [Ctract] ); go
--create a route to the remote service, we know it'll be called rem_s1 create route [rem_s1_route] with service_name = 'rem_s1', --broker_instance = 'D8EE8A81-F1B0-46B3-BBEB-70F19EF59083', address = 'TCP://127.0.0.1:4021'; go
--as we'll be doing encrypted dialogs we need a remote service binding --and the user is the user we created in the 2_setup_objects_server2_db.sql scripts CREATE REMOTE SERVICE BINDING [myRms] TO SERVICE 'rem_s1' WITH USER = remdlguser2, ANONYMOUS=Off go
--give the user send rights on the service grant send on service::rem_s2 to remdlguser2; go SELECT * from q2;
RECEIVER 2:
use [master]; go
--make sure master had master key create master key encryption by password = 'masterhello2'; go
create certificate TrpCertServ3 with subject = 'Transport Certificate for Server3', start_date = '06/01/2005'; go
--make sure the cert exist select * from sys.certificates; go
BACKUP CERTIFICATE TrpCertServ3 TO FILE = 'c:amitOfficialService Brokercerts3TrpCertServ3Pub.cer'; go
--you need to create an endpoint in order to enable communication --outside of this instance
CREATE ENDPOINT SSB3 STATE = STARTED AS TCP ( LISTENER_PORT = 4023 ) FOR SERVICE_BROKER ( AUTHENTICATION = CERTIFICATE TrpCertServ3, ENCRYPTION = REQUIRED ); go
--check that the endpoint has been created select * from sys.endpoints; go
--create a login and a user which you eventually will assign a public --key from the cert in the remote master db to create login remconnlogin3 with password = 'pass1234$'; go
create user remconnuser3 from login remconnlogin3; go
--grant connect on the endpoint to the login grant connect on endpoint::SSB3 to remconnlogin3 go
--copy in the public cert from server 1 to somewhere on this server
--create a certificate from the public cert from server 1 create certificate TrpCertServ1Pub authorization remconnuser3 from file = 'c:amitOfficialService Brokercerts3TrpCertServ1Pub.cer'; use master; go
--create the database create database [rem_ssb3]; go
use [rem_ssb3]; go
--set master key create master key encryption by password = 'hellodb3'; go
create certificate DlgCertServ3Db3 with subject = 'DlgCertServ3Db3', start_date = '06/01/2005' active for begin_dialog = on; go
--make sure the cert exist select * from sys.certificates; go
BACKUP CERTIFICATE DlgCertServ3Db3 TO FILE = 'c:amitOfficialService Brokercerts3DlgCertServ3Db3Pub.cer'; go
--create a user which you eventually will assign a public --key from the cert in the remote db to create user remdlguser3 without login; go
--copy in the public cert from server 1 to somewhere on this server
--create a certificate from the public cert from server 1 create certificate DlgCertServ1Db1Pub authorization remdlguser3 from file = 'c:amitOfficialService Brokercerts3DlgCertServ1Db1Pub.cer';
--create the message contract --and define who sends what CREATE CONTRACT [Ctract] ( [sendmsg] sent by initiator, [recmsg] sent by target ); go
--create the queue, at this stage we do not care --about activation CREATE QUEUE q2 with status = ON; go
--we need a service CREATE SERVICE [rem_s2] on queue q2 ( [Ctract] ); go
--create a route to the remote service, we know it'll be called rem_s1 create route [rem_s1_route] with service_name = 'rem_s1', --broker_instance = 'D8EE8A81-F1B0-46B3-BBEB-70F19EF59083', address = 'TCP://127.0.0.1:4021'; go
--as we'll be doing encrypted dialogs we need a remote service binding --and the user is the user we created in the 2_setup_objects_server2_db.sql scripts CREATE REMOTE SERVICE BINDING [myRms] TO SERVICE 'rem_s1' WITH USER = remdlguser3, ANONYMOUS=Off go
--give the user send rights on the service grant send on service::rem_s2 to remdlguser3; go
We have a couple of databases set up, and we replicate data from certain tables between the two database. One of the tables we replicate is the Users table, in which we'd like to encrypt user passwords. Initially I created a certificate on both servers, and found that I could not DecryptByCert a password that was encrypted on the other server, and vice versa. It looks like all I was forgetting to do with supply a 'ENCRYPTION BY PASSWORD = ' parameter to CREATE CERTIFICATE. So, now I have the following:
CREATE CERTIFICATE Cert_UserPassword ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y' WITH SUBJECT ='TestingCertificate'
I ran that query on both of our servers, and I find I am able to decrypt the password on both servers. So, as far as I can tell, this is exactly the way I want it to work.
So, now for the question: Is this the right way to go about it? In order to decrypt the password on either server, it means I need to pass the 'pGFD4bb925DGvbd2439587y' password to the DecryptByCert command, which doesn't seem very secure. But if I don't use the 'ENCRYPTION BY PASSWORD', then the cert will be signed by the Master key, which is different on both servers, which will result in a certificate that can't decrypt what was encrypted on the other server.
Is there a way to take the actual certificate on one server, and export it to the other server, so that they're both using the exact same certificate to encrypt and decrypt? I would like to not have the password included in the Decryption command, if I can help it.
I am unable to get my snapshot agent to initialize. When I start the "View Snapshot Agent Status" and press the Start button, it appears that the snapshot agent attempts to starts and then it doesn't.
I have configured the a distribution server and need to push a replication to another database, both can connect, etc.
I used the wizard to configure the distributon server and subscription server. I am using AdventureWorks, and am attempting to replicate a single small table.
hi, I have problem initialize report server after upgrade to SP2.. It keep saying Could not locate entry i sysdatabases 'myDatabaseName'. while i'm sure my database existing. Here's the log
<Header> <Product>Microsoft SQL Server Reporting Services Version 9.00.3161.00</Product> <Locale>en-US</Locale> <TimeZone>Malay Peninsula Standard Time</TimeZone> <Path>C:Program Files (x86)Microsoft SQL ServerMSSQL.2Reporting ServicesLogFilesReportServerService__07_12_2007_11_38_15.log</Path> <OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName> <OSVersion>5.2.3790.65536</OSVersion> </Header> ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file. ReportingServicesService!library!4!12/07/2007-11:38:15:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file. ReportingServicesService!resourceutilities!4!12/07/2007-11:38:15:: i INFO: Reporting Services starting SKU: Enterprise ReportingServicesService!resourceutilities!4!12/07/2007-11:38:15:: i INFO: Evaluation copy: 0 days left ReportingServicesService!library!8!12/07/2007-11:38:16:: i INFO: Catalog SQL Server Edition = Enterprise ReportingServicesService!library!8!12/07/2007-11:38:16:: i INFO: Current DB Version C.0.8.40, Instance Version C.0.8.54. ReportingServicesService!library!8!12/07/2007-11:38:16:: i INFO: Starting upgrade DB version from C.0.8.40 to C.0.8.54. ReportingServicesService!library!8!12/07/2007-11:38:16:: e ERROR: Exception caught while starting service. Error: System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database 'CCGM_'. No entry found with that name. Make sure that the name is entered correctly. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script) at Microsoft.ReportingServices.Library.ConnectionManager.EnsureCorrectDBVersion() at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage() at Microsoft.ReportingServices.Library.ServiceController.ServiceStartThread() ReportingServicesService!library!8!12/07/2007-11:38:16:: e ERROR: Attempting to start service again...
The encrypted value for "logoncred" configuration setting cannot be decrypted. (rsFailedTodecryptConfigInformation) ***********************************************************************************
Please help thank you very much for the information.
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.
--BACKUP CERTIFICATE EncryptTestCert -- TO FILE = N'c:backupEncryptTestCert.cer' -- WITH PRIVATE KEY -- ( FILE = N'c:backupEncryptTestCert.pvk', -- ENCRYPTION BY PASSWORD = N'T0yp0calypse' -- )
[Code] .....
However, the return data just contains nulls, instead of the original decrypted data. You can see above that I deleted the certificate, but then restored the certificate from backup. However, it doesn't work.
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?
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.
I've had a SSL wildcard certificate successfully in use, unless it had to be replaced because of its age. Now I've imported a new certificate, but unfortunately I'm unable to set it up in the SSRS Configuration Manager Console (Web Service URL / Report Manager URL), because "We were unable to create the certificate binding".
Here is the Error Message:
Microsoft.ReportingServices.WmiProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. Error Code 80070520 ---> System.Runtime.InteropServices.COMException (0x80070520): A specified logon session does not exist. It may already have been terminated. (Exception from HRESULT: 0x80070520) --- End of inner exception stack trace ---
I've also deleted the existing bond in the netsh console with "netsh>http delete sslcert ipport=0.0.0.0:443", but this still doesn't fix the issue.
Hi There is a very sensitive table that holds a very sensitive field (i.e. a person's salary). I have a requirement to programmatically encrypt it when the value is stored (I can do that quite easily in the VB client app), but there are times when a suitably 'sa' user should be allowed to perform a SELECT of the table and to view the salary field in the clear.
I think a SQL2000 user-defined function can do the job. The catch is that the client is running SQL 7 and he will only upgrade 'over his dead body', or words to that effect.
I understand that Dialog Security + certificates can be used to encryption individual dialogs. I have several demos working now that do just this.However, I don't fully understand exactly when the messages are encrypted, and if they are ever written to a queue on the initiating service prior to being encrypted. I want to make sure that admins can't simply query the transmission queues to get clear text messages, because I have strict requirements that I encrypt all personal data that is stored anywhere in a database. BOL is a little unclear on this topic. The relevant doc reads:For a dialog that uses security, Service Broker encrypts all messages sent outside a SQL Server instance. Messages that remain within a SQL Server instance are never encrypted. In dialog security, only the database that hosts the initiating service and the database that hosts the target service need to have access to the certificates used for security. That is, an instance that performs message forwarding is not required to have the capability to decrypt the messages that the instance forwards.Does this imply that message destined for an external service aren't encrypted until they leave the instance? Or does Service Broker figure out that the message is destined for a remote service and therefore applies encryption to the message_body prior to the message hitting the transmission queue on the initiating service?Many thanks, Kevin
I have a question regarding Transparent Data Encryption. I have enabled TDE on a database using the below steps:
1. Create a master key encryption by a password. 2. Create a certificate in the the user database named 'TDE_Test' protected by the master key. 3. Create database encryption key 'TDE_Test' using the certificate created in Step 2. 4. Enable encryption by using the command 'ALTER DATABASE TDE_Test SET ENCRYPTION ON'
I understand that if I need to copy this TDE encrypted database to a different SQL Instance, I have to copy the certificate from the source Instance to the destination Instance.Now my question is, do the Service Master Key and Database Master Key come into the picture here anywhere?
Are these related to TDE in any way? Do I have to take regular backups of the Service Master Key & Database Master Key as part of regular maintenance for the SQL Instance that has a TDE encrypted database?
I am using SQL server 2012 Management studio and I have some confidential data on publisher which is being replicated to subscriber and i want to revoke permission for decryption at publisher end which is only possible using Asymmetric key as it allows only private key to decry-pt the data. But problem which i am facing is,we can not take backup of asymmetric keys which i could restore at subscriber. I do not want to share the private key password with sender. Is there any way to achieve it?
I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!' CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs' CREATE SYMMETRIC KEY ERKEY WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
Currently running Vista Home Premium 64 and Visual Studio 2008 Trial.
1. Create Win form app. 2. Add new data source... 3. New connection - SQL Server Compact 3.5 - Northwind.sdf 4. Highlight Products and Suppliers. 5. Drag both onto Win form 6. Run with debug 7. Error message "Unable to load DLL 'sqlceme35.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)"
some blogs advice change dir in SQL Server Compact 3.5 in regedit but regedit doesn't even have SQL Server Compact 3.5; only SQL ServerSQLExpress.
reinstall SQL Server Compact 3.5 and problem still exists.
I currently have SQL2005 Std, using 2 separate domain accounts for both SQL server and SQL agent. When accessing the MSDB stored packages folder from mgmt studio, I get the following error:
Client unable to establish connection. Encryption not supported on SQL server.
I did google for this error, but have tried all suggestions, including updating the MsDtsSvr.ini.xml file with the servername. I am using a default instance.
Also, I am only able to access the MSDB folder if I set the SQL service account to the local admin account. I have also set the SQL service account to be part of the local admins group, but without any luck.
i have created one package in production server called User_Import,It will fetch the info from excel file to the Sql table, I have executed this package in ssis console successfully,But i have to schedule one job using this package on daily basis for that i have created on sql job using this package, Then it is failing i dont know the exact problem,I have full access to my database and full access to the sql agent to exuete any jobs,I have sharing the error message which am getting in the sql agent level, Please find the error msg:
05/11/2015 15:10:20,User_Imports,Error,1,SFRFIDCSCDB003PSQCM03,User_Imports,AD_User Load,,Executed as user: SFRSA-SFR-SQCM-02. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 15:10:20 Error: 2015-05-11 15:10:20.41 Code: 0xC0011007 Source: {8E9D75BC-AA22-4366-9AC5-1507DA7AB21B}
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2015-05-11 15:10:20.41 Code: 0xC0011002 Source: {8E9D75BC-AA22-4366-9AC5-1507DA7AB21B}
Description: Failed to open package file "C:UserssccmadminDocumentsVisual Studio 2008ProjectsUser_ImportsUser_ImportsUser_Imports.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "C:UserssccmadminDocumentsVisual Studio 2008ProjectsUser_ ImportsUser_ ImportsUser_ Imports.dtsx" because of error 0xC0011002.
Description: Failed to open package file "C:Userssccmadmin DocumentsVisual Studio 2008 Projects
User_ImportsUser_ImportsUser_Imports.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. Source: {8E9D75BC-AA22-4366-9AC5-1507DA7AB21B} Started: 15:10:20 Finished: 15:10:20 Elapsed: 0.015 seconds. The package could not be found. The step failed.,00:00:00,0,0,,,,0
