Is The Certificate Based Authentication The Only Way ?
Jul 26, 2007
1.On an virtual server two virtual machines of Windows 2003+sql server 2005 developer edition named VM1 and VM2
2.The VMs are not on a domain.
3.endpoint uses windows authentication
I get the following error
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://x.x.x.x:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
and the log file contains the following
07/26/2007 19:21:22,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 65.'. [CLIENT: x.x.x.x]
07/26/2007 19:21:19,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 65.'. [CLIENT: x.x.x.x]
07/26/2007 19:21:17,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 65.'. [CLIENT: x.x.x.x]
07/26/2007 19:21:14,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 65.'. [CLIENT: x.x.x.x]
07/26/2007 19:21:12,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 65.'. [CLIENT: x.x.x.x]
07/26/2007 19:21:10,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 65.'. [CLIENT: x.x.x.x]
07/26/2007 19:21:09,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 65.'. [CLIENT: x.x.x.x]
07/26/2007 19:21:08,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 65.'. [CLIENT: x.x.x.x]
07/26/2007 19:21:06,spid25s,Unknown,Database mirroring connection error 5 'Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 65.' for 'TCP://x.x.x.x:5022'.
07/26/2007 19:21:06,spid25s,Unknown,Error: 1474<c/> Severity: 16<c/> State: 1.
07/26/2007 19:21:06,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 65.'. [CLIENT: x.x.x.x]
Is there anyway i can do mirroring without certificates,
guys any pointers?
View 7 Replies
ADVERTISEMENT
May 24, 2006
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!
View 1 Replies
View Related
Aug 30, 2007
Hi Experts,
I have a reporting scenario, where the reports are fetched from Analysis Services.
The reports should display data only spcecific to that user.
All users except those in admin roles should be validated using the Windows Authentication ID and data specific to them has to be displayed.
Any pointers/suggestions on how to implement this in Reporting services/ Analysis Services 2005 would be highly appreciated
Thanks,
View 1 Replies
View Related
Nov 19, 2007
Hi,
I would like to know how to call a report from a report viewer control in a web application. The reporting services is forms authenticated. I've done this authentication using the sample solution given by microsoft (adding custom security extension). Now am able to view the reports from Report manager and Report server URL. But i dont know how to authenticate the user from my web application and pass the credentials to the report server to view the report in report viewer control. Can somebody help in this? its bit urgent. Thanks in advance
View 1 Replies
View Related
Jun 29, 2007
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.
Any help is appreciated. Thank you,
View 4 Replies
View Related
Feb 6, 2008
Well, I have SQL Server 2005 SP2 Reporting Services installed and configured in sharepoint integrated mode, so I am deploying reports in sharepoint libraries. Also I configured sharepoint to use forms authentication with builtin AspNetSqlMembership provider, but after this action I couldnot open reports through sharepoint library, instead of this, error was displayed mentioned below. I searched and found hotfix Cumulative Update 3 build 3186 for this issue, request it from microsoft support and applied it, but nothing has changed!
Error I have been experiencing is following:
An unexpected error occurred while connecting to the report server.
Verify that the report server is available and configured for
SharePoint integrated mode. --> The request failed with the error
message:
--
<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="/_layouts/login.aspx?ReturnUrl=
%2f_vti_bin
%2fReportServer%2fReportService2006.asmx">here</a>.</h2>
</body></html>
I continued to dig and find out that this hotfix should update Microsoft.ReportingServices.SharePoint.UI.WebParts.dll from version 9.0.3042.0 to new version 9.0.3180.0, but when I checked file version after applying hotfix, it remained the same as was before i.e. 9.0.3042.0. Apparently this cumulative update does not fix this issue.
anyone has any idea how to solve it? Thanks in advance.
sorry for my poor english :-)
View 2 Replies
View Related
Aug 22, 2007
I folks.I Have installed sql server 2005 express and choosed windowsauthentication on instalation, but i make a mistake and now i needmixed authentication, how can i modify this whithout uninstall andinstall again the application?thanks for the help.
View 2 Replies
View Related
May 11, 2007
How to give authentication for Send Mail Task component?
View 4 Replies
View Related
Apr 18, 2007
Hi there,I have installed MS SQL Server 2005 on my machine with windows authentication. But now I want to switch the authentication mode to SQL Authentication. I am unable to switch, I can’t find the proper way to do so here in 2005.Could any one help me in doing this?Thank you,-Ahsan
View 1 Replies
View Related
Mar 25, 2004
Hello,
(Using win2k, sqlserver2k, framework 1.1)
I have an fairly data-heavy application that uses Windows authentication (Trusted connection/aspnet account) to connect to Sql Server. The site uses IIS basic authentication.
On the dev server everything works fine but when I move to the live server things get strange and it starts to crawl along. (Pages load OK but then it just crawls as it loads the datagrids etc. Sometimes it brings back incomplete/incorrect data )
BUT When I use Sql Authentication to connect to Sql Server and there is no problem at all!
Ok, there is something obviously wrong with the live server (which is identical setup to dev)but I dont know where to start.
Any ideas??
View 2 Replies
View Related
May 15, 2006
Hi all,
I've got two applications which both have a database on my MS SQL 2000 server. The problem is, one application must use Windows Integrated Authentication (which it is currently using and cannot be changed) whilst the other application which I'm trying to configure must use a SQL password.
Since the server has already been configured to use Windows Integrated Authentication for the existing database and application, how do I configure the other database to use the SQL password?
Thanks.
View 1 Replies
View Related
May 12, 2006
Hi all,
My work is using a shared application
which accesses a MSSQL 2000 database. To access the application, the
folder on the Windows 2003 Server is shared and users can access the
folder through a shared drive.
For the application to access the
database, it uses an ODBC connection to the MSSQL server which
originally used the SA password.
We have recently switched to using
Windows Integrated Authentication because we believe it offers a
higher level of security. However the only way in which we have been
able to enable this is to add the windows users to the SQL server.
The problem with this is that the
application sets permissions for individual users on what records
they can see within the database. We have found that by adding the
windows users to the SQL Server, they can bypass the permissions the
set by the application by simply using any application that can use
an ODBC connection, such as Enterprise Manager, and see all the
database.
One way around this would be to set up
domains of users with access privileges to the tables which reflect
the permissions set by the application, and configuring a view of the
data so they may only see the records that they have permissions to.
However to do this would require a high administrative cost to ensure
that changes made in the application are reflected in the privileges
of the SQL server.
Instead, is there a way the SQL server
can authenticate that the ODBC connection is coming from the correct
application using Windows Integrated Authentication?
This would allow the applcation to
determine security, and stop users from connecting to the SQL server
using other applications.
Alternatively, can the SQL server,
using Windows Integrated Authentication, also ask the application to
supply a username and password?
Any help with this matter would be
greatly appreciated.
Thanks!
View 4 Replies
View Related
Aug 25, 2006
Hi,I'm using SQL Server 2005. My Connection String looks like that at the moment: <add name="LocalSqlServer" connectionString="Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx" providerName="System.Data.SqlClient"/> Now I'd like to change this kind of authentication to Integrated Windows AuthenticationI added the WorkerProcess IIS_WPG to the permitted Users but it didn't help.Changed the Connection String to this:connectionString="Server=xx;Database=xx;Trusted_Connection=True;"All I'm getting is that my NetworkService is not permitted to access DB when I try to connect to the DB in ASP.NET.How can I properly configure that? Thanks!
View 4 Replies
View Related
May 8, 2003
Hello
Can anyone tell me what is the difference between sql authentication and windows authentication.
Examples of each would be very useful
Many thanks in advance
Steve
View 2 Replies
View Related
Oct 16, 2006
Would anyone please help me out here. which of the 2 modes of authentication is better and why??
View 3 Replies
View Related
Dec 18, 2006
Hi,
Say, I have configured my SQL to use Mixed Authentication. Now, I have a applicaiton which uses my SQL Server. The application just creates a database in SQL Server and uses the database to store its information.
This application also has a SYSTEM DSN under ODBC through which it accesses the database. For the application to access this database, should I only use SA (as my SQL instance is configured to use Mixed Authentication) or can I use Windows Authentcation too...
If I should only use SA, do we have a documentation which talks about this.
Thanks
Santhosh
View 1 Replies
View Related
Mar 12, 2008
For using different services of SQL SERVER 2005 which is better...
Windows Authentication or SQL Server Authentication?
what are the advantages and disadvantages of both?
View 5 Replies
View Related
Feb 1, 2008
Hi.
I wonder if it is possible to set forms authentication for report manager but leave report server "as it is". I need to authenticate users from external LDAP and can't use windows authentication for report manager, but I would also like to leave report server open for anonymous users. In that way authenticated administrators could create reports which anonymous users could read.
I tested the Security Extension Sample and got it working when I rewrote the authentication part with my own LDAP authentication.
If I have understood correctly, the report manager is just application inside report server so is it possible to use forms authentication with one application but still leave the report server with Windows authentication?
View 1 Replies
View Related
Mar 4, 2008
Hello all,
can ayone tell me which exam i have to pass to get sql 2005 certificate andi n which site or link will get more information.
would be appreciated .
thanks folks.
View 2 Replies
View Related
Apr 19, 2007
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
View 2 Replies
View Related
Mar 10, 2006
Can anybody can tell me how to do self-signed certificate on sql server? What is it ? Do we really need?
Many thanks.
View 12 Replies
View Related
Dec 24, 2007
Hi,
does anybody have MSDBA certificate? I need some additional information on time, money costs, advantages, disadvantages, literature, etc.
I've browsed my local Microsofr partner as well as microsoft homepage, and haven't found any "interesting" feedback :)
thank you in advance
View 1 Replies
View Related
Apr 27, 2007
im trying to drop all certificates on my database, and then the master key, but cannot do this as there are objects encrypted by one particular cert that i called fcert. I done this ages ago and cannot remember what i encrypted with this cert. to drop the master key i have to drop the cert and to drop the cert i have to make sure no objects are encrypted by it. how is this achieved?
View 1 Replies
View Related
Apr 22, 2008
i'm totally stuck, i know i must be missing something, just can't find it.. here's my situation:
tbl_user (ssn char(9), ssn_encrypted varchar(9));
CREATE CERTIFICATE AUTOCERT WITH SUBJECT = 'Salad', EXPIRY_DATE = '1/1/2099';GO
UPDATE tbl_userset ssn_encrypted = encryptbycert(cert_id('AUTOCERT'),SSN)go
select ssn, ssn_encrypted from tbl_user
results look good. encryption worked.
SELECT ssn, decryptbycert(cert_id('AUTOCERT'),ssn_encrypted) FROM tbl_user
results of select statement show ssn_encrypted = null for every record.
why is it null? how can i get decryption to work?
View 4 Replies
View Related
Mar 6, 2008
I can use "CREATE CERTIFICATE" to add a CER certificate into a SQL database. How can I do so with a PFX certificate? From the document, it can be done so with a CER file and a PVK file, but only a PFX file.
View 1 Replies
View Related
Dec 6, 2006
hey,
I am
having a weired issue(donno whether its weired or not.). I have a user
who has db_owner rights on a database. But when he is trying to create
a certificate he is getting error.
"Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action."
Remember he is having db_owner rights on that particular database. is there any other permission that i have to give him.
View 5 Replies
View Related
Apr 28, 2008
Hi i'm having issues removing this certificate
select * from sys.certificates
I see
cert_xp_cmdshell_enabler 257 1 NA NO_PRIVATE_KEY 1 Enable xp_cmdshell c5 30 7d 58 ba 8e 96 a6 48 48 df ca 87 bc 91 44 0x010600000000000901000000BEC4CB23E7994CFF1E71403F906BB1EFFDDEAF9A S-1-9-1-600556734-4283210215-1061187870-4021382032-2595217149 Enable xp_cmdshell 2009-04-28 10:31:31.000 2007-04-28 10:31:31.000 0xBEC4CB23E7994CFF1E71403F906BB1EFFDDEAF9A NULL
DROP CERTIFICATE cert_xp_cmdshell_enabler
he certificate cannot be dropped because one or more entities are either signed or encrypted using it.
Is there any way I can drop this as I would like to recreated it and add a whole host of sps that I need to use the xp_cmdshell.
Thanks in advance.
View 4 Replies
View Related
Dec 22, 2006
Hi guys,
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.
Anyone have any ideas?
View 3 Replies
View Related
Sep 30, 2006
Hello:
I am trying to get SQL Express to use a certificate for SSL encryption between the server and client. Our company has a certificate server and I already did generate a server certificate from it. I installed the certificate in the "local machine" store and I also have the CA certificate in the trusted root on the server machine. (XP PRO)
When I use the configuration tool, I get to the "protocols for SQLEXPRESS" area, I click properties, I select the certificate tab, but there are no certificates available from which to choose.
Can someone shed some light on where I might go from here?
Larry
View 3 Replies
View Related
Jun 30, 2006
Hello,
I have two different instances of sql server 2005 but i get
Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.
This is one of the two instances:
use master
--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'
create master key encryption by password = 'hello'
create certificate [Certificato2]
from file = 'c:certsTransportCert2.cer'
with private key (FILE='c:certsTransportCert2.pvk',
decryption by password='simone')
active for begin_dialog = ON
CREATE LOGIN [M02] WITH PASSWORD = 'wrPqYkr%bm3';
ALTER LOGIN [M02] DISABLE;
CREATE USER [M02] FROM LOGIN [M02];
GO
create certificate [Certificato1]
authorization [M02]
from file = 'c:certsTransportCert1.cer'
active for begin_dialog = ON
GO
USE PublisherdDB
--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'
create master key encryption by password = 'hello'
create certificate [CertificatoDialogo2]
from file = 'c:certsDialogCert2.cer'
with private key (FILE='c:certsDialogCert2.pvk',
decryption by password='simone')
active for begin_dialog = ON
CREATE USER [Proxy::IsDbLookupRequestServiceM02] WITHOUT LOGIN;
GO
create certificate [CertificatoDialogo1]
authorization [Proxy::IsDbLookupRequestServiceM02]
from file = 'c:certsDialogCert1.cer'
active for begin_dialog = ON
CREATE REMOTE SERVICE BINDING [RSB::IsDbLookupRequestServiceM02]
TO SERVICE 'IsDbLookupRequestServiceM02'
WITH USER = [Proxy::IsDbLookupRequestServiceM02],
ANONYMOUS = OFF;
GO
CREATE ROUTE [Route::IsDbLookupRequestServiceM02,D516E70B-59D6-4BF4-882A-BDA7ACD6EB07] WITH
SERVICE_NAME = 'IsDbLookupRequestServiceM02',
ADDRESS = 'tcp://PORTATILEXP:4022';
GO
GRANT SEND ON SERVICE::[IsDbLookupResponseService] TO [Proxy::IsDbLookupRequestServiceM02]
GO
USE MASTER
CREATE ENDPOINT [BROKER]
AUTHORIZATION [VIDEOSYSTEMSimone_Farinea]
STATE=STARTED
AS TCP (LISTENER_PORT = 4033, LISTENER_IP = ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = CERTIFICATE [Certificato2]
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GRANT CONNECT ON ENDPOINT::[BROKER] TO [M02];
Here is the second one:
use master
--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'hello'
create master key encryption by password = 'hello'
create certificate [Certificato1]
from file = 'c:certsTransportCert1.cer'
with private key (FILE='c:certsTransportCert1.pvk',
decryption by password='simone')
active for begin_dialog = ON
CREATE LOGIN [SIMONEX] WITH PASSWORD = 'wrPqYkr%bm3';
ALTER LOGIN [SIMONEX] DISABLE;
CREATE USER [SIMONEX] FROM LOGIN [SIMONEX];
GO
create certificate [Certificato2]
authorization [SIMONEX]
from file = 'c:certsTransportCert2.cer'
active for begin_dialog = ON
GO
USE vsi
--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'hello'
create master key encryption by password = 'hello'
create certificate [CertificatoDialogo1]
from file = 'c:certsDialogCert1.cer'
with private key (FILE='c:certsDialogCert1.pvk',
decryption by password='simone')
active for begin_dialog = ON
CREATE USER [Proxy::IsDbLookupResponseService] WITHOUT LOGIN;
GO
create certificate [CertificatoDialogo2]
authorization [Proxy::IsDbLookupResponseService]
from file = 'c:certsDialogCert2.cer'
active for begin_dialog = ON
GRANT SEND ON SERVICE::[IsDbLookupRequestServiceM02] TO [Proxy::IsDbLookupResponseService]
GO
CREATE ROUTE [Route::IsDbLookupResponseService,88EB00C4-8CA9-4B45-9899-677AA70818B1] WITH
SERVICE_NAME = 'IsDbLookupResponseService',
ADDRESS = 'tcp://SIMONEX:4033';
GO
USE MASTER
CREATE ENDPOINT [BROKER]
AUTHORIZATION [VIDEOSYSTEMSimone_Farinea]
STATE=STARTED
AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = CERTIFICATE [Certificato1]
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GRANT CONNECT ON ENDPOINT::[BROKER] TO [SIMONEX];
What's wrong in my code?
Many thanks.
View 1 Replies
View Related
Nov 1, 2007
As we know ,certificate is a digitallly-signed security object that bind the public key to the principal who holds the private key.
Say i create a certificate by using create certificate DDL, then use it to encrypt data and decrypt data as follows:
Code Block
create certificate cert1 encryption by password='p@ssw0rd1' with subject='certificate test'
go
declare @plaintext varchar(100)
set @plaintext='abcd'
declare @cipher varbinary(8000)
set @cipher=encryptbycert(cert_id('cert1'),@plaintext)
select convert(varchar,decryptbycert(cert_id('cert1'),@cipher,N'p@ssw0rd1'))
Is that mean i create public key implicitly when i run "create certificate cert1 encryption by password='p@ssw0rd1' with subject='certificate test'"?
What cryptography algorithm would be use when i use the certificate to encrypt data ?
Where is private key? I ensure I use the public key which generated from "create certificate " DDL to encrypt data .I want to know which private key i used when i execute decryptbycert to decrypt.
View 7 Replies
View Related
Feb 19, 2007
Hello I haw trouble getting the service broker to work I have 3 instances of SQL servers:
1 €śSender€? SQL 2005 Server
2 €śReceiver 1€? SQLEXPRESS 2005
3 €śRecevier 2€? SQLEXPRESS 2005
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 what´s 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'
--DROP CERTIFICATE Cert_Andonpc017_Trans
CREATE CERTIFICATE Cert_Andonpc017_Trans AUTHORIZATION andon
FROM FILE = 'C:Cert_andonpc017_Trans'
--DROP LOGIN sbLogin
CREATE LOGIN sbLogin
FROM CERTIFICATE Cert_Andonpc017_Trans;
GO
GRANT CONNECT ON ENDPOINT::SBEndpointServer TO [public]
GRANT CONNECT ON ENDPOINT::SBEndpointServer TO andon
GO
----------------------------------------------------
USE AndonDB
CREATE ROUTE Grafik_sevapc311
WITH SERVICE_NAME = 'Grafik_Service_Recive_sevapc311',
BROKER_INSTANCE = '7C737F42-2DF6-46E7-A6B6-89D1A9608DE2',
ADDRESS = 'TCP://sevapc311:5723'
GO
--DROP ROUTE Grafik_andonpc017
CREATE ROUTE Grafik_Andonpc017
WITH SERVICE_NAME = 'Grafik_Service_Recive_Andonpc017',
BROKER_INSTANCE = 'AE2B294A-B02E-4709-A51E-CFBFD0E478C1',
ADDRESS = 'TCP://192.168.20.106:5723'
GO
CREATE CERTIFICATE Cert_ROBOTSRV_Dialog
WITH SUBJECT = 'Cert_ROBOTSRV_auth',
START_DATE = '02/15/2007',
EXPIRY_DATE = '02/15/2015'
GO
BACKUP CERTIFICATE Cert_ROBOTSRV_Dialog TO FILE = 'C:Cert_ROBOTSRV_Dialog'
GO
CREATE CERTIFICATE Cert_sevapc311_pub_Dialog AUTHORIZATION andon
FROM FILE = 'C:Cert_sevapc311_Dialog'
--DROP CERTIFICATE Cert_andonpc017_Dialog
CREATE CERTIFICATE Cert_Andonpc017_Dialog AUTHORIZATION andon
FROM FILE = 'C:Cert_andonpc017_Dialog'
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'
CREATE CERTIFICATE Cert_sevapc311
WITH SUBJECT = 'Cert_sevapc311_Auth',
START_DATE = '02/15/2007',
EXPIRY_DATE = '02/15/2015'
GO
BACKUP CERTIFICATE Cert_sevapc311 To FILE = 'C:Cert_sevapc311'
CREATE ENDPOINT SBEndpointklient STATE = STARTED
AS TCP (LISTENER_PORT = 5723)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_sevapc311)
CREATE CERTIFICATE Cert_ROBOTSRV_pub AUTHORIZATION andon
FROM FILE = 'C:Cert_ROBOTSRV';
GRANT CONNECT ON ENDPOINT::SBEndpointklient to andon
----------------------------------------------------------------------------------
use KlientDB
GRANT SEND ON SERVICE::[Grafik_Service_Recive_sevapc311]
TO andon
GO
GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_sevapc311]
TO andon
GO
GRANT RECEIVE ON [Grafik_Queue]
TO andon
GO
CREATE Route Grafik_ROBOTSRV
WITH
SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV',
BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481',
ADDRESS = 'TCP://ROBOTSRV:5723'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'
CREATE CERTIFICATE Cert_sevapc311_Dialog
WITH SUBJECT = 'Cert_sevapc311_Auth',
START_DATE = '02/15/2007',
EXPIRY_DATE = '02/15/2015'
GO
BACKUP CERTIFICATE Cert_sevapc311_Dialog To FILE = 'C:Cert_sevapc311_Dialog'
CREATE CERTIFICATE Cert_ROBOTSRV_pub_Dialog AUTHORIZATION andon
FROM FILE = 'C:Cert_ROBOTSRV_Dialog';
€śReceiver 2€?
use master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'
--DROP CERTIFICATE Cert_Andonpc017_Trans
CREATE CERTIFICATE Cert_Andonpc017_Trans
WITH SUBJECT = 'Cert_Andonpc017_Auth',
START_DATE = '02/15/2007',
EXPIRY_DATE = '02/15/2015'
GO
BACKUP CERTIFICATE Cert_Andonpc017_Trans To FILE = 'F:Cert_Andonpc017_Trans'
CREATE ENDPOINT SBEndpointklient STATE = STARTED
AS TCP (LISTENER_PORT = 5723)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_Andonpc017_Trans)
--DROP USER andon
CREATE USER andon
--DROP CERTIFICATE Cert_ROBOTSRV
CREATE CERTIFICATE Cert_ROBOTSRV AUTHORIZATION andon
FROM FILE = 'C:Cert_ROBOTSRV';
CREATE LOGIN sbLogin
FROM CERTIFICATE Cert_ROBOTSRV;
GO
GRANT CONNECT ON ENDPOINT::SBEndpointklient TO [public]
--Select * from sys.certificates
---------------------------------------------------------------
use KlientDB
create user andon
Grant SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017] to [Public]
GRANT SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017]
TO andon
GO
GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_Andonpc017]
TO andon
GO
GRANT RECEIVE ON [Grafik_Queue]
TO andon
GO
--DROP Route Grafik_ROBOTSRV
CREATE Route Grafik_ROBOTSRV
WITH
SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV',
BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481',
ADDRESS = 'TCP://ROBOTSRV:5723'
--Dialog Säkerhet
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'
--Drop CERTIFICATE Cert_Andonpc017_Dialog
CREATE CERTIFICATE Cert_Andonpc017_Dialog
WITH SUBJECT = 'Cert_Andonpc017_Auth',
START_DATE = '02/15/2007',
EXPIRY_DATE = '02/15/2015'
GO
BACKUP CERTIFICATE Cert_Andonpc017_Dialog To FILE = 'F:Cert_Andonpc017_Dialog'
--Drop CERTIFICATE Cert_ROBOTSRV_Dialog
CREATE CERTIFICATE Cert_ROBOTSRV_Dialog AUTHORIZATION andon
FROM FILE = 'C:Cert_ROBOTSRV_Dialog';
View 2 Replies
View Related
Feb 7, 2007
I have been trying to create a certificate for use with SQL2005. I found openSSL to create a cert but I am not sure how to use it.
When I go into SQL Config Manager / Protocol Properties / Certificate Tab... I do not see any certificates. The list is empty. Where are these certs pulled from and how can I create one on my own?
Here are the Reqs:
Certificate Requirements
For SQL Server 2005 to load a SSL certificate, the certificate must meet the following conditions:
The certificate must be in either the local computer certificate store or the current user certificate store.
The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
View 3 Replies
View Related
