Help configuring SQL Server 2008 Database

I am running Visual Studio 2008. I have installed SQL server 2008 and upgraded my SQL server 2000 Db, which works with my ASP.NET 2.0 App on my local machine. I want to install the Db on Discountasp but I want to do all the checks before I purchase the Db add-on:

1. Will my connection string be as follows?
<add name="myCS" connectionString="Data Source=tcp:esql2k801.discountasp.net;Initial Catalog=myDb;User ID=myUser;Password=********;" providerName="System.Data.SqlClient"/>

2. Will DiscountASP be providing me with the user name or should I create the roles and users on my Db? If so, how will DiscountASP configure the user with the required minimal security privlieges?

3. Will DiscountASP be providing me with the Db name or will it be the same as on my Db?

4. When I want to update the Db and App with changes, what tools can I use to minimise the workload in the following process (I believe there are tools to do this, I have no experience with the MS versions of these)?
[FONT=&quot]1.[FONT=&quot] [/FONT][/FONT][FONT=&quot]Stop website[/FONT]
[FONT=&quot]2.[FONT=&quot] [/FONT][/FONT][FONT=&quot]Backup database: get latest copy of information[/FONT]
[FONT=&quot]3.[FONT=&quot] [/FONT][/FONT][FONT=&quot]Make updates to new databases based on the backup database[/FONT]
[FONT=&quot]4.[FONT=&quot] [/FONT][/FONT][FONT=&quot]Restore database[/FONT]
[FONT=&quot]5.[FONT=&quot] [/FONT][/FONT][FONT=&quot]Make changes to website[/FONT]
[FONT=&quot]6.[FONT=&quot] [/FONT][/FONT][FONT=&quot]Start website[/FONT]

Thanks in advance for this help.

 

Hi,
Your DASP Control Panel has an IIS section where you can Stop/Start and Recycle your site, which are very useful for DB maintenance, like Forums.
You can also use VS2008 to put your remote site in standby but it is easier with the DASP Control Panel.

When you add a SQL Server 2008 DB you can specify the DB name, you will then be given a string in your account, for the SQL Server edition you added, which will list all you need.
Your added user will be the DBO and in all code or SQL you will have the added DB as the default, you can specify it or let the server default to it. I find that part helpful.

Whatever you need should not be a problem...Just repost if you need help.
All the best,
Mark

 

1. Will my connection string be as follows?
<add name="myCS" connectionString="Data Source=tcp:esql2k801.discountasp.net;Initial Catalog=myDb;User ID=myUser;Password=********;" providerName="System.Data.SqlClient"/>


This looks about right. You should also add...

<remove name="LocalSqlServer" />

2. Will DiscountASP be providing me with the user name or should I create the roles and users on my Db? If so, how will DiscountASP configure the user with the required minimal security privlieges?

We will provide you the user name and password for your database but I'm not sure what you mean by the user name for roles and users. That something you create in the ASP.Net Configuration Tool.

3. Will DiscountASP be providing me with the Db name or will it be the same as on my Db?

We will provide you the default DB name to the SQL addon you order. It will appear in your hosting control panel once the addon has been provisioned on your account. You may want to refer to this kb article for some guidelines in setting up ASP.Net Membership/Roles provider on our servers.

http://kb.discountasp.net/KB/a337/h...embershiproles-provider.aspx?KBSearchID=44611

4. When I want to update the Db and App with changes, what tools can I use to minimise the workload in the following process (I believe there are tools to do this, I have no experience with the MS versions of these)?
1.Stop website
2.Backup database: get latest copy of information
3.Make updates to new databases based on the backup database
4.Restore database
5.Make changes to website
6.Start website

These steps looks about right. Although you really do not need to stop your web site to make these changes. The best way is download your website and get a copy of your database. Do your changes in your test box on your computer and once the changes are made simply upload it in real time. All the changes should automatically take into effect without bringing down the website.

 

Thanks for the quick response. It actually makes me feel more comfortable compared to the hosts I have used in the past.

Referring back to the red highlights and points:

2.
I will create a role and associated user(s) with minimal authority that, for example, can only run the stored procedures on the Db. Will this role, and the user(s) in it, get migrated to the DiscountASP Db when uploaded? That is what I would expect, especially in a shared environment.

Whilst on the subject, what security measures can I implement on my connection string password? Of course it will be strong but what stops someone from accessing it during the ftp upload for example?

4.
I would like help with an automated way of getting the data from the existing production database into the "to be migrated" database. I have little experience with SQL Server, particularly 2008, which I migrated to because 2000 is fundamentally dead.

 

2.
I will create a role and associated user(s) with minimal authority that, for example, can only run the stored procedures on the Db. Will this role, and the user(s) in it, get migrated to the DiscountASP Db when uploaded? That is what I would expect, especially in a shared environment.

---I'm not quite sure I understand what you are asking. I had the assumption the roles you are creating are for your web applications. These users do not have any rights to your database. It is the connection string and the db login you use within that connection string that will have the associated rights to your database. Bare in mind that our web server and SQL server are separate stand alone boxes. They are both independent from each other.


Whilst on the subject, what security measures can I implement on my connection string password? Of course it will be strong but what stops someone from accessing it during the ftp upload for example?

--What hosting platform are you on? If you are on our IIS 7 platform, you should be able to use FTP over SSL. Our IIS 6 platform does not have this feature. As for the connection string, once uploaded to our web server passing the connection string to the SQL server should be fairly safe. Remember although the servers are there own boxes, they are within the same network. So any calls to each other will not traverse through the internet but within a local area network. But if you still want to encrypt your connection string, we do allow this. You may want to read this kb article.

http://kb.discountasp.net/KB/a339/h...sections-your-webconfig.aspx?KBSearchID=44868

4.
I would like help with an automated way of getting the data from the existing production database into the "to be migrated" database. I have little experience with SQL Server, particularly 2008, which I migrated to because 2000 is fundamentally dead.

Use the SQL Management Tool to upload your local database to our SQL Server.
http://kb.discountasp.net/KB/a188/sql-2008-management-tools.aspx

 

In reference to your comments, the db login in the connection string is the access granted to the ASP.NET application and indirectly to the users. How do I make sure this db login has minimal privileges?

- Creating the user and role on my db before uploading it (in this case, how do I make the link in my connection string?)
- I create the user and/or roles after uploading the db
- DiscountASP creating the user (in this case, how do you ensure minimal privileges?)

 

Sorry, I found the answer:

https://support.discountasp.net/KB/...l-sql-logins-to-my-sql-2008.aspx?KBSearchID=0

How to assign an SQL login to a database:

1. Use the SQL Login Manager to create a new MS SQL login in the MS SQL 2008 Manager section of the hosting control panel
2. Connect to the database using SQL Server Management Studio with your primary login (see this Knowledge Base article)
3. Expand the Databases node
4. Locate the database to which you want to add the new SQL Login and expand the node
5. Expand the Security Node
6. Right click on the Users node and select "New User..."
7. In the Database User Properties
   • Type the username you want to assign to this login in the "User name" text box
   • Type the login you want to assign to the database in the "Login name" text box
   • In the Database role membership, select the role you want to grant this login.
8. Click OK

 

Yeah this is a good kb. Just make sure you use another SQL login other then your default main SQL login. This should typically be left as the dbo to the database.

 

In the article I posted above, I cannot access the link in item 2: http://172.16.20.82/KB/a186/how-to-connect-to-sql-server-2008-using-sql-server.aspx

I am still unsure about this issue.

I want to give the user in the connection string in my web.config, minimal privileges. Therefore I thought I should create a new login and then assign that login to the role with minimal privileges and then user the new login. Is this right?

Can someone help me with this?

 

That certainly is a dead link that needs fixing; I think this is probably the kb article it should be linking to: http://support.discountasp.net/KB/a...r-2008-using-sql-server.aspx?KBSearchID=45586

Hopefully this step by step description will simplify things a little bit for you:

1) Your database has already been provisioned, therefore you already have a SQL login account for your database; this is the SQL login account that is automatically created by DASP when the database is initially provisioned.
2) Create a new SQL Login for your database in MSSQL Management Studio by following the steps detailed in this kb article you have already found: https://support.discountasp.net/KB/...l-sql-logins-to-my-sql-2008.aspx?KBSearchID=0
3) As part of step (2) you will use the default DASP created default SQL login account to connect to your DASP SQL Server using MSSQL Management Studio by following the steps detailed here: http://support.discountasp.net/KB/a...r-2008-using-sql-server.aspx?KBSearchID=45586. Once logged in you will create the corresponding SQL Server user.
4) Now you have a new SQL Login created for your database, assign the minimal role(s) you need for that user in MSSQL Management Studio. You can create your own roles if necessary for fine control over permissions, but you'll find there are many already pre-configured in SQL Server.
5) If necessary take ownership of database objects with MSSQL Management Studio as all current database objects will be owned by [dbo] (the default SQL Login account). In addition if necessary you can assign specific permissions to individual database objects by assigning database role(s) or user(s) to database objects.
6) Modify the db connection string in your web.config to use the new username / password.

In Microsoft / SQL Server speak, Principals are the individuals, groups, and processes granted access to SQL Server. Securables are the server, database, and objects the database contains. Each has a set of permissions that can be configured to help reduce the SQL Server surface area.

That's my quick whistle stop tour of SQL Server security, but there's heaps more information on MSDN and this is a good place to start: http://msdn.microsoft.com/en-us/library/bb510418.aspx

Good luck, HTH.
Joe