I wanted to provide a little more concrete example of what I have been talking
about with the database changes needed for the passive STS. I have a prototype
that is using a new database that I have named StonehengePasssiveSts. In the
StonehengePassiveSts database I have created one table and populated it with
some sample data.
Here is the create table statement from SQL Server Management Studio.
CREATE TABLE [dbo].[Users](
[UserId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[EncryptedPassword] [varbinary] (500) NOT NULL,
[PasswordSalt] [varbinary] (50) NOT NULL,
[UserEmail] [nvarchar](250) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[Address1] [nvarchar](250) NOT NULL,
[Address2] [nvarchar](250) NULL,
[City] [nvarchar](50) NOT NULL,
[StateProvince] [nvarchar](50) NOT NULL,
[PostalCode] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_UserId] DEFAULT (newid())
FOR [UserId]
GO
Here are some statements that I used to populate values for the first 3 users
(uid:0, uid1, and uid2). (Note at this point the encrypted password and salt
values are just place holders and will be replaced by real encrypted and salted
versions of the xxx password later).
INSERT INTO dbo.Users(UserName, EncryptedPassword, PasswordSalt, UserEmail,
FirstName, LastName, Address1, City, StateProvince, PostalCode)
VALUES('User0', convert(varbinary (500), '<encryptedvalue>'), convert(varbinary
(50), '<encryptedsalt>'), '[email protected]',
'Sample', 'User0', '111 First Street', 'Redmond', 'WA', '98033')
GO
INSERT INTO dbo.Users(UserName, EncryptedPassword, PasswordSalt, UserEmail,
FirstName, LastName, Address1, City, StateProvince, PostalCode)
VALUES('User1', convert(varbinary (500), '<encryptedvalue>'), convert(varbinary
(50), '<encryptedsalt>'), '[email protected]',
'Sample', 'User1', '111 First Street', 'Redmond', 'WA', '98033')
GO
INSERT INTO dbo.Users(UserName, EncryptedPassword, PasswordSalt, UserEmail,
FirstName, LastName, Address1, City, StateProvince, PostalCode)
VALUES('User2', convert(varbinary (500), '<encryptedvalue>'), convert(varbinary
(50), '<encryptedsalt>'), '[email protected]',
'Sample', 'User2', '111 First Street', 'Redmond', 'WA', '98033')
GO
In the existing StockTraderDB database I added a new table that I named
AccountStsUser. It has just an account ID and STS user ID. Here is the
corresponding data.
AccountID StsUserId
1 9DF8E4CA-BB9F-46BB-8ACD-4D6F6872B37C
5 70F44ACF-E4D4-4153-B978-4740D0B1067F
11 8B32E49F-B52B-4E39-89A7-254F628ED170
I hope this makes our previous discussions on the STS and mapping STS users to
an account ID (called profileID in the cookie) a little clearer.
With this table structure all STS implementations could use the same GUID to
identify the user at the STS or each STS could come up with its own unique GUID
and place that value in the StsUserId table. As long as there were no GUID
colissions with different AccountID values then the code will work without
problem.
Please let me know if you have any comments or concerns about these database
changes.
Scott Golightly
