StrataFrame Forum

ASP security model with DDT

http://forum.strataframe.net/Topic4310.aspx

By Keith Chisarik - 11/9/2006

First of many questions I am sure.



I am adding the security tables required to use ASP.NET 2.0's membership API to a database using aspnet_regsql.exe.



I then used the import function (SQL Server) in DDT to import these tables along with my database, it gets the tables but not the views or stored procs (of which there are about 45). So of course when I deploy it blows off all that good stuff I need for my app.



So here is the rub, I want to use DDT for my web applications (website) database. My thought is that I will manually add the views and stored procs to the metadata and then be able to use the DDT exclusively from that point on. I can just use SQL Management Studio's "CREATE TO" functions to get the stored procs and view definitions.



Do you guys see any issue with this? It will be a decent amount of work up front, but then I am centralized and able to use both ASP.NET role/membership based security and Strataframe. That is my thinking.
By Keith Chisarik - 11/9/2006

Oops this belongs in WebForms.



Also, I am not using SF security Module for two reasons.



1) Is it even supported in webforms (websites) ?



2) I am somewhat familiar already with the membership/role based stuff in .NET and need to make immediate progress with the project. I haven't had time to familiarize myself with SF Security yet even tho we purchased it.



Thanks.
By Trent L. Taylor - 11/9/2006

Do you guys see any issue with this?

Nope...this is the way to go...we will be adding the import support for views, etc. later on.  This is on the enhancement requests list.

1) Is it even supported in webforms (websites) ?

Yes, it is supported.  There is a topic in the help about this...you have to logon by manually calling the AuthenticateUser() method, etc...but you can use it there.

I am somewhat familiar already with the membership/role based stuff in .NET and need to make immediate progress with the project. I haven't had time to familiarize myself with SF Security yet even tho we purchased it.

It is really up to you which direction to go...you would be a better candidate of determining which avenue would be suite your needs.

By Keith Chisarik - 11/9/2006

If anyone ever needs it, here is a package file equivalent to running aspnet_regsql.exe to create ASP.NET 2.0 security tables, views, and stored procs.
By Keith Chisarik - 11/10/2006

1) Does the DDT not import relationships and indexes when it imports from SQL Server or did I do something wrong? (see image)



If not can I add these manually using code like I can for stored procs? I am not 100% confident I can rebuild these exactly with using the CREATE TO function....



ALTER TABLE [dbo].[aspnet_Applications] ADD UNIQUE NONCLUSTERED

(

   [ApplicationName] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]



2) Also it appears that if I run a DDT "deploy" against an existing database that I ran aspnet_reqsql.exe against that DDT only attempts to modify what it does (tables, views, stored procs), is this right? Can I feel ok that if I recreate the indexes and relationships as in question 1 above, that the schemas, database roles and other things aspnet_reqsql.exe creates will be left intact by the DDT? Here is my deploy log from running a DDT Deploy against a database that was modified with aspnet_reqsql.exe. It looks to me like all is is doing "wrong" is a result of not importing the relationships and indexes. Agree?



• 11/10/2006 11:08:27 AM   -> Deploying meta-data for database 'BayshoreColdwell'

• 11/10/2006 11:08:27 AM   -> Ensuring Filegroups exist on database 'BayshoreColdwell'

• 11/10/2006 11:08:27 AM   -> The datafile 'BayshoreColdwell' was successfully altered.

• 11/10/2006 11:08:27 AM   -> Filegroup verification complete.

• 11/10/2006 11:08:27 AM   -> Ensuring Tables exist on database 'BayshoreColdwell'

• 11/10/2006 11:08:29 AM   -> The table 'aspnet_Applications' is being prepared to be modified.

• 11/10/2006 11:08:29 AM   -> The relationship 'FK__aspnet_Me__Appli__145C0A3F' was successfully dropped.

• 11/10/2006 11:08:29 AM   -> The relationship 'FK__aspnet_Pa__Appli__45F365D3' was successfully dropped.

• 11/10/2006 11:08:29 AM   -> The relationship 'FK__aspnet_Ro__Appli__32E0915F' was successfully dropped.

• 11/10/2006 11:08:29 AM   -> The relationship 'FK__aspnet_Us__Appli__0425A276' was successfully dropped.

• 11/10/2006 11:08:30 AM   -> The index 'aspnet_Applications_Index' was successfully dropped.

• 11/10/2006 11:08:30 AM   -> The index 'UQ__aspnet_Applicati__00551192' was successfully dropped.

• 11/10/2006 11:08:30 AM   -> The index 'UQ__aspnet_Applicati__7F60ED59' was successfully dropped.

• 11/10/2006 11:08:31 AM   -> The table 'aspnet_Applications' was successfully altered.

• 11/10/2006 11:08:36 AM   -> The table 'aspnet_Membership' is being prepared to be modified.

• 11/10/2006 11:08:36 AM   -> The index 'aspnet_Membership_index' was successfully dropped.

• 11/10/2006 11:08:38 AM   -> The table 'aspnet_Membership' was successfully altered.

• 11/10/2006 11:08:39 AM   -> The table 'aspnet_Paths' is being prepared to be modified.

• 11/10/2006 11:08:39 AM   -> The relationship 'FK__aspnet_Pe__PathI__4BAC3F29' was successfully dropped.

• 11/10/2006 11:08:39 AM   -> The relationship 'FK__aspnet_Pe__PathI__4F7CD00D' was successfully dropped.

• 11/10/2006 11:08:40 AM   -> The index 'aspnet_Paths_index' was successfully dropped.

• 11/10/2006 11:08:41 AM   -> The table 'aspnet_Paths' was successfully altered.

• 11/10/2006 11:08:41 AM   -> The table 'aspnet_PersonalizationAllUsers' is being prepared to be modified.

• 11/10/2006 11:08:42 AM   -> The table 'aspnet_PersonalizationAllUsers' was successfully altered.

• 11/10/2006 11:08:43 AM   -> The table 'aspnet_PersonalizationPerUser' is being prepared to be modified.

• 11/10/2006 11:08:44 AM   -> The index 'aspnet_PersonalizationPerUser_index1' was successfully dropped.

• 11/10/2006 11:08:44 AM   -> The index 'aspnet_PersonalizationPerUser_ncindex2' was successfully dropped.

• 11/10/2006 11:08:44 AM   -> The table 'aspnet_PersonalizationPerUser' was successfully altered.

• 11/10/2006 11:08:46 AM   -> The table 'aspnet_Profile' is being prepared to be modified.

• 11/10/2006 11:08:47 AM   -> The table 'aspnet_Profile' was successfully altered.

• 11/10/2006 11:08:48 AM   -> The table 'aspnet_Roles' is being prepared to be modified.

• 11/10/2006 11:08:48 AM   -> The relationship 'FK__aspnet_Us__RoleI__37A5467C' was successfully dropped.

• 11/10/2006 11:08:48 AM   -> The index 'aspnet_Roles_index1' was successfully dropped.

• 11/10/2006 11:08:49 AM   -> The table 'aspnet_Roles' was successfully altered.

• 11/10/2006 11:08:50 AM   -> The table 'aspnet_SchemaVersions' is being prepared to be modified.

• 11/10/2006 11:08:51 AM   -> The table 'aspnet_SchemaVersions' was successfully altered.

• 11/10/2006 11:08:52 AM   -> The table 'aspnet_Users' is being prepared to be modified.

• 11/10/2006 11:08:53 AM   -> The relationship 'FK__aspnet_Me__UserI__15502E78' was successfully dropped.

• 11/10/2006 11:08:53 AM   -> The relationship 'FK__aspnet_Pe__UserI__5070F446' was successfully dropped.

• 11/10/2006 11:08:53 AM   -> The relationship 'FK__aspnet_Pr__UserI__29572725' was successfully dropped.

• 11/10/2006 11:08:53 AM   -> The relationship 'FK__aspnet_Us__UserI__36B12243' was successfully dropped.

• 11/10/2006 11:08:53 AM   -> The index 'aspnet_Users_Index' was successfully dropped.

• 11/10/2006 11:08:53 AM   -> The index 'aspnet_Users_Index2' was successfully dropped.

• 11/10/2006 11:08:54 AM   -> The table 'aspnet_Users' was successfully altered.

• 11/10/2006 11:08:54 AM   -> The table 'aspnet_UsersInRoles' is being prepared to be modified.

• 11/10/2006 11:08:55 AM   -> The index 'aspnet_UsersInRoles_index' was successfully dropped.

• 11/10/2006 11:08:55 AM   -> The table 'aspnet_UsersInRoles' was successfully altered.

• 11/10/2006 11:08:59 AM   -> The table 'aspnet_WebEvent_Events' is being prepared to be modified.

• 11/10/2006 11:09:01 AM   -> The table 'aspnet_WebEvent_Events' was successfully altered.

• 11/10/2006 11:09:01 AM   -> Table verification complete.

• 11/10/2006 11:09:01 AM   -> Dropping unneeded filegroups for 'BayshoreColdwell'

• 11/10/2006 11:09:01 AM   -> Unneeded filegroups dropped on database 'BayshoreColdwell'

• 11/10/2006 11:09:01 AM   -> Ensuring Views exist on database 'BayshoreColdwell'

• 11/10/2006 11:09:03 AM   -> View verification complete.

• 11/10/2006 11:09:03 AM   -> Ensuring Indexes exist on database 'BayshoreColdwell'

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Applicati__7E6CC920' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Membershi__1367E606' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Paths__44FF419A' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Personali__4AB81AF0' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Personali__4D94879B' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Profile__286302EC' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Roles__31EC6D26' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_SchemaVer__08EA5793' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Users__03317E3D' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_UsersInRo__35BCFE0A' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_WebEvent___5FB337D6' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> Index verification complete.

• 11/10/2006 11:09:03 AM   -> Ensuring Foreign Keys exist on database 'BayshoreColdwell'

• 11/10/2006 11:09:03 AM   -> Foreign Key verification complete.

• 11/10/2006 11:09:03 AM   -> Ensuring Assemblies exist on database 'BayshoreColdwell'

• 11/10/2006 11:09:03 AM   -> Assembly verification complete.

• 11/10/2006 11:09:03 AM   -> Ensuring Stored Procedures exist on database 'BayshoreColdwell'

• 11/10/2006 11:09:04 AM   -> Stored Procedure verification complete.

• 11/10/2006 11:09:04 AM   -> Finished verifying databases on server.

• 11/10/2006 11:09:04 AM   -> The meta data conversion to the SQL Server has been completed.
By Keith Chisarik - 11/10/2006

Image of missing index and relationships.......
By Trent L. Taylor - 11/10/2006

If you create the relationships and indexes through SCRIPT then you will be directly defying how the DDT is designed and you will start to see deployment issues.  Importing the relationships and indexes is part of the enhancement requests that I mentioned in a previous post.

If you manually create the relationships and indexes, then you will see proper behavior from that point forward.  Since we use SMO and logically detect and update relationships, indexes, etc., the DDT relies on the meta-data in order to know whether these items are part of the schema and how to deal with them.

So yes, I agree that your problems are stemming from the indexes and relationships not being imported.

By Keith Chisarik - 11/10/2006

Can you confirm DDT will not blow away existing Schemas, roles, etc that is doesn't try to manage?
By Trent L. Taylor - 11/10/2006

The DDT only works within the confines of what it knows...so if you have a table you are attempting to update, it needs to know the indexes and relationships to manage them.

But if you manually create a table and the DDT doesn't know about it, it should leave it alone.  There is one confine that it will smoke everything...and that is if you delete the database in the DDT.  Obviously if it deletes the database because you have removed it in your DDT package and there is a history record that tells it to Delete, then it doesn't care about anything else...it will just delete the database.

In short, you should be fine, but I would copy over your ive database and test this first to make sure there isn't something being overlooked.

By Keith Chisarik - 11/10/2006

You are correct. It does leave anything alone that is doesn't know about. This is great.



It means I wasted 3 hours manually entering views and stored procs, but thats OK as I am happier with this anyhow.



Leave the security stuff alone, use DDT for application table management only.



Brilliant! Smile



Thanks for the help.
By Trent L. Taylor - 11/10/2006

No problem Smile
By Keith Chisarik - 11/10/2006

Update:



If you have a manually added table in your database that the DDT knows nothing about and do a deploy it happily ignores it. Good.



If you have an object in your database that DDT doesn't mess with at all (schemas, triggers, roles) and do a deploy it ignores them. Good.



If you have a manually added stored proc or view in your database, not defined in the DDT and do a deploy it deletes them. Bad



Hope this helps.
By Trent L. Taylor - 11/10/2006

Yes...that is true.  If you add a single sproc or view, it has to "smoke" the existing ones before it can re-add the new one.