One SQL 2005 Database AddOn or two?

I am a new user of DiscountASP.net, just opened my account today. I have purchased one SQL 2005 Database Addon, I am currently waiting for it to be provisioned. In the meantime, a question.

I have created a web site using Visual Studio 2008 and SQL Server Express 2005. It allows a user to register, log on, and view content that can only be viewed by logged-on users. It uses the ASP.NET Membership framework. Everything is working fine on my local machine.

I copied my web site to the DiscountASP.net server, and I'm seeing the expected Login control. Of course attempting to actually log in fails dramatically because I have not provisioned the hosted database yet. I'll save any further discussion of this until I have been notified that the SQL Database is available, and have an opportunity to provision.

My web site hosts a second database for managing the actual site content. So in my App_Data folder, I have 2 sets of database files: ASPNETDB.MDF (the membership database) and ProjectGiga.MDF (the site content database).

Here is my question:
Do I need to purchase 2 SQL 2005 Database Addons? One for the Membership database, and one for the ProjectGiga database?
Or will I be able to attach both to the single SQL 2005 Database Addon?

Regards,

Art
Gray Matters Software

 

Membership/Roles Database

I have tried to follow the instructions in the article "How to configure the ASP.NET 2.0 Membership/Roles Provider to use SQL 2000, SQL 2005 or SQL 2008."

It is not clear to me exactly what is meant by replacing

"<connectionStrings/>"

with

"<connectionStrings>
<remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Data Source=DBServerName;Integrated Security=false;Initial Catalog=DBName;User ID=DBLogin;Password=DBPassword" providerName="System.Data.SqlClient" />
</connectionStrings>".

I have treated "DBServerName", "DBName", "DBLogin", and "DBPassword" as variables, substituting the values given in my DiscountAsp.net control panel (under "MS SQL 2008 Databases").

But what about "LocalSqlServer"? Is it a variable, or a literal? If it is a variable, what value should I substitute for it?

Should the above script come after the connection string for the main database? If so, should the "<connectionStrings>" tag be inserted as shown above, meaning that it will occur twice?

Should the server name be preceded by "tcp:", as in the connection string for the main database?

Is it important for these changes to the web.config file to be made before the VWD configuration tool for users and roles is used?

Thank you for your trouble.

Greek

 

Hi,
Post the Conn string from your Control Panel, comment out the password and username.
Then I'll rewrite it for you.
Make sure you post the "name" you are using in code.
If you don't want to post it in the forums it is OK to PM me, or Email [email protected] .
All the best,
Mark

 

Users, Roles, Permissions

Dear Mark,

Thank you very much for your offer of help.

The connection string prescribed in my DASP control panel is as follows, where expressions in pointed brackets (including the brackets themselves) are variables:
"Data Source=tcp:<SQL Server Name>;Initial Catalog=<Database Name>;User ID=<Database Login>;Password=<Password>;"
My database file is Survey.mdf. I tried using the following code in the root web.config file of the site on my local machine, before creating users, roles, and permissions:
<connectionStrings>
<add name="SurveyConnectionString" connectionString="Data Source=tcp:<SQL Server Name>;Initial Catalog=<Database Name>;User ID=<Database login>;Password=<Password>;"
providerName="System.Data.SqlClient" />
<remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Data Source=tcp:<SQL Server Name>;Integrated Security=false;Initial Catalog=<Database Name>;User ID=<Database Login>;Password=<Password>" providerName="System.Data.SqlClient" />
</connectionStrings>

No ASPNETDB.MDF file was created.

I uploaded the local site to the DASP server and attached the Survey.mdf file to the database.

I am using Visual Web Developer 2008 Express, which seems not to allow use of the ASP.NET Configuration tool on a remote site. So I couldn't figure out how to set up users, roles, or permissions directly on the remote site.

After attaching the database file, I ran aspnet_regsql.exe from my local Command Prompt. It seemed (in DOS) to work fine.

When I pulled up the web site in my browser (IE), I was not able to log in, but at least no exceptions were thrown. (I did not expect to be able to log in, since there was no ASPNETDB.MDF file, but with other versions of the <connectionStrings> section of the \web.config file I did get exceptions.)

Thank you again for your help.

With best regards,

Eric (aka Greek)

 

Hi,
I'll go through some details for you since there seems to be a bit of confusion.
That is understandable by the way, no worries...

First of all this is a valid connectioin string in one of my web.configs:
<connectionStrings>
<clear/>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=tcp:sql.discountasp.net;Initial Catalog=msftwise;User ID=msftwise_user;Password=****" providerName="System.Data.SqlClient"/>
<remove name="SQL2005_msftwise_userConnectionString"/>
<add name="SQL2005_msftwise_userConnectionString" connectionString="Data Source=tcp:sql.discountasp.net;Initial Catalog=SQL2005_msftwise;Persist Security Info=True;User ID=msftwise_user;Password=***" providerName="System.Data.SqlClient"/>
<!--
<remove name="MyAccessDB"/>
<add name="MyAccessDB" connectionString="~/_database/Details.mdb" providerName="System.Data.OleDb"/>
-->
</connectionStrings>

OK, now notice at the top there is a CLEAR command...
Then I use Add and Remove...
I also add a trick to use LocalSqlServer as my actual remote SQL Server's name.
Then at the bottom notive a valid Access DB conn string but it is commented out, this is for testing when needed.

There are several tricks shown above, you should try each of them except the Access DB and how it is commented out, that part is just for an example.

Now as for your Survey.mdf you do not use it once it is attached in your Control Panel.
It is no longer needed on the site.
You can not make hits to an Express SQL Server DB on DASP servers.

Now finally...the aspnet_regsql while it worked for you, which is great, there are things that can go wrong because the Attach feature will over-write and to use the aspnet_regsql tool you should do that after your Attach.

You can download SQL Server 2008 for free and take a look at your DASP SQL Server DB if you need. The 2008 SQL Express versions are the first to allow some very nice remote features which were not available in the past.
All the best,
Mark

 

Configuration

Dear Mark,

Thank you very much for your advice.

I have tried several variations based on your example, but none of them worked.

I wonder whether the problem is that users and permissions have to be set up on the remote server. If that is the case, do you know how to do that with VWD Express? Or do I have to upgrade to the full version?

Thank you for your trouble.

With best regards,

Eric

 

Hi,
VWD does a good job.
Looks like you may need someone to take a peek at your SQL Server DB and your site's root web.config to get everything working.
If that is the case and you can't find anyone let me know.
All the best,
Mark

 

Thanks

Hi Mark,

Thank you for your efforts to help me.

I have found some material in the literature from which I think I will be able to figure out how to solve this problem.

With best regards,

Eric

 

Hi,
Good to know. Thanks for being so friendly. ;-)
All the best,
Mark

 

Hi Mark,

Your initial response has helped me to finally solve the problem, but I had to read and try out a few other things before I understood how to apply it. So thank you very much for your help.

With best regards,

Eric