Need some help with setting up database for website, lenghty post.

Question

Need some help with setting up database for website, lenghty post.

jonathanalvestad

SSC Journeyman

Points: 80
More actions
April 18, 2012 at 9:44 am

#256572

Hi, I have some questions regarding setting up a database.
I'm in a student group working on a project, our project needs a website with possibilities to make orders...so far:
-Made a webpage in ASP.NET (Up and running)
-Local Server/Host machine (Win 7, IIS, SQL Server 2008 R2)
-Made stand alone software for reading/writing of data to a database file.
-Made a simple order page (You check for reservations, and put in orders/booking)
But since none of us have ANY training in configuring databases or websites, it's mostly consisted of reading threads and tutorials...and here's where to problems start pouring in. So I have some general questions.
How do I set up a database so that Clients visiting the web page AND someone from a stand alone application can reach (read/write to) the database? Multiple users. What would be the best way to do this?
What should the connection string look like? Is there anything I should do in IIS?
If I understand correctly, I should attach the database to SQL Server, and then give permission/rights to the different users?
Does it matter where the physical location of the database is on the computer?.
Thanks!

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

jonathanalvestad (4/18/2012)
Hi, I have some questions regarding setting up a database.
How do I set up a database so that Clients visiting the web page AND someone from a stand alone application can reach (read/write to) the database? Multiple users. What would be the best way to do this?

The connections are what the application uses to connect, not the individual users. You should create a database user for the website and another for internal applications. They will have different permissions at some point.

What should the connection string look like?

http://www.connectionstrings.com/%5B/url%5D

Is there anything I should do in IIS?

Not sure what you mean here. IIS does not have anything to do with your database.

If I understand correctly, I should attach the database to SQL Server, and then give permission/rights to the different users?

Yes

Does it matter where the physical location of the database is on the computer?

Again not really sure what you mean here. The database will be on the server with SQL server. This IS a different machine than where you are running your website right???

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jared SSC Guru Points: 61793 More actions · Answer 2

I assume you are using VS to do this? If so, you can add tables to your existing database, or create one. The best way to explain database design is through talking out what you need; i.e. I have customers who place orders of products. BOOM! You need a customer table, an order table, and a product table (at the least). You know that a customer relates to orders, so a link has to be made between those tables (customerNum). An order relates directly to products, so another link needs to be in those tables (i.e. orderNum or something).... See where I am going with this?

Jared
CE - Microsoft

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 3

Some things come to mind in considering a project like this.

Obviously its a big topic but handling security for multiple users will differ according to their roll and I think its beneficial to consider this early on. For instance, you'll have the "company" folks who will have their security defined much more by what roll they play in the company, ie., they may enter data for products, so they'll have access to all the products, or maybe they enter order info, so they'll have to have access to insert orders for the company. On the other hand, customers will need access to similar info but ONLY when it is keyed to their own account, ie., they should NOT have access to other customers records, ie., row level security instead of table level security. Then of course, you'll have the usual multiuser concerns, ie., for the web you'll have to track logins and sessions at the application layer, whereas at the windows application you can probably rely more on operating system and database facilities.

jonathanalvestad SSC Journeyman Points: 80 More actions · Answer 4

Thanks!

The SQL Server machine and web host machine are the same, this is strictly a test project...i.e, we will never actually use it. And because of 0/LOW$ budget, we could only use one computer for it all.

How do I make a login for the two different users?

-The web content is in one folder on the machine (This account will be for the website visiters, right?) called wwwroot

-The stand alone application(s) must be able to reach/connect the database from different computers. In other words.

I attached the database to SQL server in SSMS, but when trying to create new Logins under "Security" in SSMS, I'm honestly not sure which users to choose from the list that comes up in the search.

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

You can create your own logins if you are using sql authentication (ie. the database connection will provide a username/password).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jared SSC Guru Points: 61793 More actions · Answer 6

You want to create a user for the database admins, and 1 for the application. Then, use the Asp.net security that is built in to manage the application credentials.

Jared
CE - Microsoft

Perry Whittle SSC Guru Points: 234064 More actions · Answer 7

jonathanalvestad (4/18/2012)
Hi, I have some questions regarding setting up a database.
I'm in a student group working on a project, our project needs a website with possibilities to make orders...so far:
-Made a webpage in ASP.NET (Up and running)
-Local Server/Host machine (Win 7, IIS, SQL Server 2008 R2)
-Made stand alone software for reading/writing of data to a database file.
-Made a simple order page (You check for reservations, and put in orders/booking)
But since none of us have ANY training in configuring databases or websites, it's mostly consisted of reading threads and tutorials...and here's where to problems start pouring in. So I have some general questions.
How do I set up a database so that Clients visiting the web page AND someone from a stand alone application can reach (read/write to) the database? Multiple users. What would be the best way to do this?
What should the connection string look like? Is there anything I should do in IIS?
If I understand correctly, I should attach the database to SQL Server, and then give permission/rights to the different users?
Does it matter where the physical location of the database is on the computer?.
Thanks!

It depends on how complex your web app is. Are you using .NET roles and memberships at all or just a vanilla website connecting to a database?

Does the web site have a login box or do users go straight to a web page?

What is the stand alone application, i'm guesing its a .NET windows form is that correct?

The web.config file would normally hold the connection string. I would have one SQL Server account for the web iste connection and another for the stand alone app.

If you get stuck and can\want to arrange the remote access i would be happy to provide a little support for you to help you on your way.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 8

Don't forget about backups.

Sql Server doesn't have an undo button, and your time is valuable. The 15 minutes it will take for you to configure backups could save you hours of work if you mess things up. Often, it is quicker to restore from a backup than to try to cleanup a mess.

if you're doing this for a class project, chances are you'll have a tiny amount of data. If so, then Full Backups hourly. I'm intentionally glossing over some really important things here, but this is better than not backing up your data at all. (If you want a production quality recommendation, then ask and it shall be provided. )

This advice will prevent the "help! how to do I undelete a table in sql server? all my data is gone!" post the night before your project is due.

good luck!

jonathanalvestad SSC Journeyman Points: 80 More actions · Answer 9

Hi again.

So I attached the database to the server.

Added Data Connection in Visual Studio (Server Explorer)

iboat\sqlexpress.C:\USERS\IFLOAT\DESKTOP\WEBSITE3 - SVEIN-EDITION\APP_DATA\RESERVASJON.MDF.dbo

Connection string I used is

"Data Source=IBOAT\SQLEXPRESS;Initial Catalog=C:\USERS\IFLOAT\DESKTOP\WEBSITE3 - SVEIN-EDITION\APP_DATA\RESERVASJON.MDF;Integrated Security=True"

When debugging the project in VS, I can write to and retrieve data from the database, no problem.

However, when I build to project and test it out online, I get errors.

Cannot open database "C:\USERS\IFLOAT\DESKTOP\WEBSITE3 - SVEIN-EDITION\APP_DATA\RESERVASJON.MDF" requested by the login. The login failed.

Login failed for user 'IIS APPPOOL\DefaultAppPool'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open database "C:\USERS\IFLOAT\DESKTOP\WEBSITE3 - SVEIN-EDITION\APP_DATA\RESERVASJON.MDF" requested by the login. The login failed.

Login failed for user 'IIS APPPOOL\DefaultAppPool'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Cannot open database "C:\USERS\IFLOAT\DESKTOP\WEBSITE3 - SVEIN-EDITION\APP_DATA\RESERVASJON.MDF" requested by the login. The login failed.

Login failed for user 'IIS APPPOOL\DefaultAppPool'.]

System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +6389568

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412

System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2660

System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +53

System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) +6410190

System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +6410169

System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +352

System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +831

System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +49

System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +6411974

System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +78

System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +2194

System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +89

System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +6415502

System.Data.SqlClient.SqlConnection.Open() +300

Bestilling.BtnSjekkSted_Click(Object sender, EventArgs e) +113

System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +154

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3707

Finally, I can't crate a IIS_IUSRS login (I take it that this is the login I need for the webroot map? Since it's under IIS) in SQL Server.

The error I get there is

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 10

"C:\USERS\IFLOAT\DESKTOP\WEBSITE3 - SVEIN-EDITION\APP_DATA\RESERVASJON.MDF" is not really a good database name, more likely the database name to use is just 'RESERVASJON' or whatever name you gave while issueing "create database" statement. Theres at least that you can be working on.

jonathanalvestad SSC Journeyman Points: 80 More actions · Answer 11

I copied the database from that folder, and pasted it to a C:\DB\ folder.

The file itself is just named reservasjon.mdf, but whenever I attach it to SQL Server the other stuff is added to the name. Any idea why?

Bah, the more I search and read the more confusing it gets.

Jared SSC Guru Points: 61793 More actions · Answer 12

jonathanalvestad (4/19/2012)
I copied the database from that folder, and pasted it to a C:\DB\ folder.
The file itself is just named reservasjon.mdf, but whenever I attach it to SQL Server the other stuff is added to the name. Any idea why?
Bah, the more I search and read the more confusing it gets.

So rename it 🙂

Jared
CE - Microsoft

jonathanalvestad SSC Journeyman Points: 80 More actions · Answer 13

Okay, so I renamed it (just to RESERVASJON.MDF), it still works when debugging in VS (localhost server), but when I build the website and upload it I still get this error when trying to write or read information to/from the database.

Could it have something to do with the security logins?

The website is stored in C:\inetput\wwwroot\ , which again is connected to IIS.

Server Error in '/' Application.

Cannot open database "RESERVASJON.MDF" requested by the login. The login failed.

Login failed for user 'IIS APPPOOL\DefaultAppPool'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open database "RESERVASJON.MDF" requested by the login. The login failed.

Login failed for user 'IIS APPPOOL\DefaultAppPool'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Cannot open database "RESERVASJON.MDF" requested by the login. The login failed.