Confusion about dialog security steps.

Hi There

I have done the following.

2 Servers across the net work Server A and Server B.

I have created private keys at each and copied to public keys across.

I create an endpoint at each with validation by the certificates. I then create appropriate users and logins at each and import the public certificates with authorization to the users who have send permission on the endpoints.

That is transport security sorted and it works.

Now from what i have learnt from examples, to setup dialog security i do the following.

create a private key in each DB :

create certificate Store001DialogPri

with subject = 'Store001DialogPri',

start_date = '07/20/2006'

active for begin_dialog = on;

go

I then copy the public key to each server, create a user only in each DB and import the public keys with authorization on the user. And grant send to the appropriate servcies to the user.

I then create appropriate remote service bindings with this user.

Now this works for me. Everythign seems A OK.

However i am going thorugh the Service Broker "bible", and there are a ton of steps i am not doing but my setup works ?

Steps i am not doing for example is when i create the private keys in the DB i should authorize them to a user i create who is then gratnted CONTROL permission on the SERVICES.

Now i do not do this but everything seems to be working. I thought i finally understood dialogs security but now i am totally confused ?

Is what i am doing correct ? If so why are these all these additinal steps mentioned ? WHat am i missing ?

Thanx

The AUTHORIZATION and CONTROL steps in the samples are needed if you are performing these steps for somebody else. You test probably ended up with all the services being owned by [dbo], (you are sysadmin on the test machine, aren't you?), and the certificates with private keys also being owned by [dbo], so it just worked. But in real life, you might have to do this setup steps for a service owned by a different database user, so in that case is important to associate the private key with the service owner.

HTH,
~ Remus

|||

Hi Remus

Yes everything should be owned by dbo, however i do not see any otehr users coming into the SB application, so therefore i shouldnot have to do these steps?

Thanx for the feedback

Confused with primary keys and foreign keys

Hi, I am currently doing a database project for my company. The database contains 5 tables inside, let's say A,B,C,D,E. The primary key of table A is referenced by all other tables. If an instance is created in other tables except in table A, it means that the foreign keys are not null. However, if an instance is created in table A, it doesn't necessarily mean that the primary key of that particular instance must be referenced by another instance in other tables. In other words, the foreign keys in other tables have null value.

Now, the question is how can I display this primary key in table A together with primary keys of other tables since some of them may not even exist yet? By displaying all the primary keys, I can therefore check that the primary keys in other tables still don't exist and with this, I can input a value to it.

P.S. I am connecting the database with VB 2005 Express Edition and I always get blank value when I tried to retrieve the value.

Thank you very much for the help

Foreign Key:

Is the advanced check constraint which will check the set of values from the another table. The another/reference table is called parent/master table & the current table is called child/detailed table. The Master table which holds the set of value should be not null unique. (you can’t have the more than one Parent for single Child). But your child table column can hold NULL value (which is not yet born)

Primary key:

Is the table levelconstraint which will ensure your table columns have a unique & not null values.

Display the Parent & child values (which is not yet born):

You have to use OUTER JOIN to achieve this. If you post more information we can provide the rite information to you.

|||

The basic problem is that IF there is not a row in the Child table (table with FK), or there is a row, but its FK value is NULL, there is no way to 'connect' the row to the Parent table (table with PK).

It is possible to 'simulate' the missing rows by using a LEFT JOIN, and referring to the Parent table first.

Try out this sample code to see how to combine tables where both tables have matching values, and also where one table is missing a FK value from the other table.

Code Snippet

--***********************************
--Problem: Demonstrate PK-FK JOINS

CREATE TABLE Parent
( RowID int IDENTITY PRIMARY KEY,
MyValue varchar(20)
)
GO

CREATE TABLE Child
( RowID int IDENTITY PRIMARY KEY,
ParentID int REFERENCES Parent(Rowid),
MyValue varchar(20)
)
GO

SET NOCOUNT ON

INSERT INTO Parent VALUES ( 'Parent1' )
INSERT INTO Parent VALUES ( 'Parent2' )
INSERT INTO Parent VALUES ( 'Parent3' )
INSERT INTO CHILD VALUES ( 2, 'Child1' )
INSERT INTO CHILD VALUES ( 3, 'Child2' )
INSERT INTO CHILD VALUES ( 2, 'Child3' )
INSERT INTO CHILD VALUES ( NULL, 'Child4' )

-- To find ONLY Parents AND their Children
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
JOIN Child c
ON p.RowID = c.ParentID

-- To find All Parents and Children (if Any)
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
LEFT JOIN Child c
ON p.RowID = c.ParentID

-- To find Parents without Children
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
LEFT JOIN Child c
ON p.RowID = c.ParentID
WHERE c.ParentID IS NULL

-- To find Children without Parents (Orphans)
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
RIGHT JOIN Child c
ON p.RowID = c.ParentID
WHERE c.ParentID IS NULL

DROP TABLE Child
DROP TABLE Parent

|||Thanks for both your suggestions, but I don't have any idea how to perform LEFT JOIN on 5 tables. Can you gimme some idea on this? Thanks..|||

Manivannan.D.Sekaran wrote:

Foreign Key:

Is the advanced check constraint which will check the set of values from the another table. The another/reference table is called parent/master table & the current table is called child/detailed table. The Master table which holds the set of value should be not null unique. (you can’t have the more than one Parent for single Child). But your child table column can hold NULL value (which is not yet born)

Primary key:

Is the table level constraint which will ensure your table columns have a unique & not null values.

Display the Parent & child values (which is not yet born):

You have to use OUTER JOIN to achieve this. If you post more information we can provide the rite information to you.

The child values are actually not null, they are only not yet born. However, I need to display both the parent table which already has a child and which has no child at all. I will try to use OUTER JOIN in the code and see how the result will be. Thanks a lot.