MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1705

This tip seems to be exactly what I need.  Additionally, I applaud the style in which it is presented!  However, when I attempt to follow the steps outlined in the tip, I encountered two issues. The second issue stopped my progress.  Let me elaborate:

A)  I started on Step 3. All previous steps were already done.  The script executed without issue.

B)  For Step 4, I copied the SQL and pasted it into a New Query. The script would not 'run' as listed in the tip. I discovered that if I preceded the listed script with  USE <database_name>    the script executed. I noted that the Step 3 script cited USE master. But I want to mirror a database named serverInfo, so I created:

USE serverInfo CREATE CERTIFICATE cslgsrvg_cert WITH SUBJECT = 'cslgsrvg certificate for database mirroring'

This script executed without error and I can 'see' the cert under serverInfo database [ SSMS:  serverInfo/Security/Certificates ]

C)  However (!) -- when I tried to execute Step 5, the script fails. I get following error:

Cannot find the object "cslgsrvg_cert" because it does not exist or you do not have permission

I am logged in as User with 'sa' permissions.

I cannot get past this point. How can I get back 'on track'? Your guidance would be appreciated.

 gary b

 Addendum: Please note that I had SQL database mirroring fully operational when both Principal *and* Mirror servers were in the same domain. But recent network changes required use of a different SQL server as the mirror -- and that server is in a different domain. Hence, my interest in mirroring across domains.

Hi,

The certificate has to be configured in the master database as you would first have to be authenticated on the server-instance level before the database level.  This is the reason why the TSQL query only uses a single USE master command from steps #3 to #11. Step #5 asks you to create an endpoint, which is a server-instance securable.  The reason the for the "certificate does not exist" error is because you are trying to grant permission to a server-instance level object using a database-level certificate. You can drop the certificate you've created on the serverInfo database and start all over again

I normally recommend to test this out using a test environment - a virtual machine or a physical lab - so as not to mess around with your production environment setup

Good morning, bass_player

I have deleted the certificate for serverInfo. I reran the CREATE CERTIFICATE script without USE serverInfo. When I then try to create the endpoint, I receive the following error message:

" Cannot find the object 'cslgsrvg_cert' because it does not exist or..."

[snip]

Added:

I ran query to display all certificates. The 'cslgsrvg_cert' was NOT listed.

 I selected master database in SSMS and executed the CREATE CERT script. If I look under Security/Certificates, I can now see the cert listed.

I reran the CREATE ENDPOINT script and... it executed successfully !  So I am going to the next steps in your list.

The only thing I did differently (between unsuccessful and successful create endpoint scripts) was to run create cert script while master database was selected in SSMS GUI. 

Will report back later.

 gary b

Will this work on a Windows 2000 server?  The reason I ask is that when I try to run the script, I get and error message saying: