Secure database administration

Have successfully preformed the following: -

a) Use SQL Server Management Studio to ?Generate scripts?? from my local D/B and then execute it on the remote D/B (sql2k501.discountasp.net)

b) Import/export data with the SQL Server Import/Export Wizard

b) I can connect to my D/B, on sql2k501.discountasp.net, and run ad hoc queries

c) Run the ASP.Net Website Administration Tool and perform very limited user account administration

Questions...

1) The problem is that I was not able to connect to sql2k501.discountasp.net with the 'Encrypt Connection', option because it is not currently supported, so I was hoping there was a way of performing these tasks without exposing my D/B login credentials?

2) Is there any standard asp.net apps with a more comprehensive range of user account administration functions for securely managing an ASPNETDB.MDF database on a remote server?

 

Hi Bruce

Thanks for the reply....

1) Is there any chance that the 'Encrypt Connection' option will be available in the near future? Otherwise, my current understanding is that every time I access my database remotely I have sent my login credentials across the internet in plain text and therefore have to change my password, which even if I use a separate D/B user to the one in my web.config file isn't ideal.

2)

a) I have already set up Forms Authentication, which works fine, but wanted a method of administering the ASPNETDB.MDF database with preferably more functionality than the standard Web Admin Tool, which would also present the same problem as described in (1), wouldn't it?

b) I have already used the Membership class in my web app and would be the ideal solution, but thought I should check whether someone, somewhere had written an open-source Management tool already, so that I wouldn?t have to start from scratch. Would be an worthwhile exercise though...

c) Unfortunately, couldn't read the link you kindly detailed in your email, but was wondering whether webadmin.axd was a pre-compiled version of the Web Admin Tool and whether it provided any extra functionality? Would be preferable to (a) as it wouldn?t present the problems described in (1).

Additional question: any chance you could advise me on how best to set up a D/B user, which can execute all stored procedures/user functions, but can?t create, modify or delete any D/B objects or access the Tables directly?

Your assistance is most appreciated.

Best regards

Robert

 

For writing a Membership Management tool (2b), the link below looks promising...

http://peterkellner.net/archives/2006/01/09/24/


Post Edited By Moderator (Joel Thoms) : 6/13/2006 1:20:25 AM GMT

 

Can access the link fine, must have copied and pasted the \ on the original link without looking = duh!

Support for secure SQL connections would, in my opinion, allow webmasters to not be the weak link in an already very secure system. Was appreciative to see the new D/B functionality because then at least I can back up my D/B before I login to the database server and in the instance someone did intercept my dbo login, before I had finished my maintenance and subsequently changed my dbo login password, then at least I could restore the original D/B. Wouldn?t prevent access to client data, but at least the most sensitive data is encrypted, but may have to consider encrypting all client data!

Just in case anyone was interested, yesterday I managed to set up my local SQL2K5 D/B with users/roles so that I didn't have to put my dbo connection string in my web.config file, or any other user connection strings that can modify database objects, but with GRANT EXECUTE ON the stored procedures and database chaining allows access to the database through only the permitted dbo owned stored procedures that in my understanding would prevent 'SQL injection' attacks too.

Next step is to get it working on the server...

 

Just thought I should follow-up and say that I got the extra users working on the database server in an identical way to my local SQL2K5 D/B, by...

1) Deleting the D/B user I had created locally to mimic the default (dbo) server D/B user.

2) Uploading my local D/B and performing an 'Attach Database' in the Control Panel's new SQL2K5 Tools (beta).

3) Modifing the connection strings from 'Database' (local) to 'Initial Catalog' (server), as well as 'Data Source' of course, but that was the case when I was using the default D/B user anyway.

4) Executing my original locally generated scripts, but with USE specifying the remote D/B, to change the default schemas of the users to a schema that owns no objects (-wasn't sure if that is what the 'Guest' Schema was?) and to add the users each to a different set of roles.

Very pleased that there were no major issues to contend with and works very nicely in combination with Forms Authentication, and means that I don't have to store any dbo connection strings in the web.config file, even if it will be encrypted.

Plus my request to support Secure SQL2K5 Connections has been forwarded to the DiscountASP.net management, which if granted would be the icing on the Secure D/B cake.