Creating MSSQL database and new user for beta website

Hi

I need some help with MSSQL Driven website.
It is placed on Virtual Dedicated Server with windows server 2008 R2 and it has following:

Access with remote desktop
SQL Server Management Studio 2008 R2
SQL Database for the running website
Two Separately accessible directories with all files (Live and Beta)

The problem is that it was setup so that both sets of files Live and Beta use the main database and access it with SA user.

I would like to be able to fully separate the data, so that the beta testers do not have the access to the LIVE data of SQL database to avoid overwriting by mistake.

The website is built in .NET (I think 3.5 or 4.0). Access is available to everything including uncompiled files.

What I did:

I created through SQL Studio on the server another user and a new database for this user (restored from the backup of the live database). Username is ‘beta’ and database name ‘beta’. Beta user can login through management studio and see the beta database without any problems.
I replaced the info in the web.config (in the beta directory) with the new access info:
<connectionStrings>
            <add name="Application" connectionString="Data Source=(local)\SQLEXPRESS;Initial Catalog=beta;User ID=beta;Password=xxxxxxxx" />
  </connectionStrings>

Where I changed three fields only: catalog (I assume it is a database name), user ID and Password. name="Application" left unchanged.

However, after running the application with the modified web.config it gives me a blank white screen after I try to login.

I’m not sure what was done wrong: maybe I can not use name ‘beta’, or something wrong with permission of DB of user or maybe there is somewhere else something else needs to be changed.

It supposed to be very quick adjustment (unless I’m mistaken), but unfortunately I can not make it work.

If I need to start from scratch and create a new database and user, I could do that. I just need some help what I would need to do.

Thank you for your help.
mimarkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JavierVeraCommented:
when you publish you should consider the ip or domain name where the SQL server is running.

ie:

<connectionStrings>
            <add name="Application" connectionString="Data Source=<<<<YOUR IP HERE >>>>>\SQLEXPRESS;Initial Catalog=beta;User ID=beta;Password=xxxxxxxx" />
  </connectionStrings> 

Open in new window


also, consider if there is any instance name configured...
as for the database if you have control over the machine and dont want to bother just stop the service manually and pick up the mdf files you want, reinstall or create another instance and attach those mdfs
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mimarkAuthor Commented:
Thank you for your reply, I will try with an IP:

- Are you suggesting to have this
connectionString="Data Source=XXX.XXX.XXX.XXX\SQLEXPRESS;
instead of
connectionString="Data Source=(local)\SQLEXPRESS; ?
But the (local) works for the LIVE database (without entering IP)

- any instance name configured
where should I look for it?
you mean "Application"?
can it be in many other files (besides web.config)?
how should and where should I look for it?

Thank you
0
JavierVeraCommented:
once publish your app should connect to the server where the sql server is running.

you should be more specific on your scenario i belive,
if you have a server where to store your application and you are already publishing it AND your sql server runs on the same server machine WITH the SAME sql server instance name.

you can find the instance name by running this on your production server:

SELECT SERVERPROPERTY('INSTANCENAME')

Open in new window


it should match the same on your connection string.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

mimarkAuthor Commented:
Thank you

I checked the instancename and it is SQLEXPRESS which is properly set in web.config connection string. Perhaps two separate databases can not have same instance name? If so, what should I do?

The MSSQL SERVER, LIVE and BETA DATABASE, LIVE and BETA applications all run on the same server. Though BETA app has it's own set of .aspx .dll files and another domain name but at the moment connected to LIVE database. The goal is to connect to BETA database.

If I need to be more specific, please let me know what would you like me to look up.

Thank you
0
JavierVeraCommented:
Hi, did you solve this? you could attach a screenshot of your error so we could tell you better. Please consider this or simply check your configs.
0
mimarkAuthor Commented:
Yes I solved it. Thank you for your help.
0
JavierVeraCommented:
Hey, maybe  you could post the answer so if anyone finds this usefull they could keep it. Anytime. peace!
0
mimarkAuthor Commented:
In addition to the connection string settings, I looked into the User Mapping for BETA user and enabled

db_owner

It started working for me.
Not sure if it is a right solution or right thing to do.

Feel free to let me know what do you think.

Thank you for your time
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.