ASP Classic Webapp - Connect to different SQL DB via login

Question

Need a way to connect to a unique SQL db via login in ASP classic.

THE SETUP

• Webapp: ASP classic/SQL 2005.
• Webapp stores information for multiple companies.
• All data stored in one master SQL 2005. All db's will be on same server.
• Each user has a unique login (Company, User ID, Password)
• Connection is with master db include file using DSN-less connection
• IE: (dbConn.Open "driver={SQL Server};server=11.22.333.444;database=mywebdb","mylogin","mypassword")

THE NEED:

Want to split companies into their own database. When the user logins, the company name will tell the APP to use a unique SQL db connection for each company.

Since dynamic include files are not an option, what is best route to go?

Thank you!

Answer 1

However you do it, you'll end up varying your connection string based on user input. Don't use user input directly, but validate it against a list of acceptable values. I suggest a Select Case statement to do this:

' Do this when logging in: '
Dim companyName
companyName = Request.Form("companyName")

Select Case companyName
Case "company1"
    Session("companyDB") = "company1"
Case "company2"
    Session("companyDB") = "company2"
Case Else
    Session.Contents.Remove("companyDB")
    ' Invalid login! '
End Select

' Do this when connecting to the database: '
Dim connectionString
If Session("companyDB") Then
    connectionString = "...database=" & Session("companyDB") & "..."
Else
    '  Invalid login, go log in again '
End If

Keep in mind that this will lead to trouble if you have users who will want to open one company in one tab and another company in another tab. They are going to wonder why they can only see information for the company they logged into most recently.

If this is going to be an issue, you will probably want to pass a token around in the query string on each link. This adds complexity, but not terribly much (aside from the tedious task of changing every link). It would then look like this:

' Do this when logging in: '
Dim companyName
companyName = Request.Form("companyName")

Select Case companyName
Case "company1"
    Session("company1 - db") = "company1DBName"
Case "company2"
    Session("company2 - db") = "company2DBName"
Case Else
    ' Invalid login! '
End Select


' Do this when connecting to the database: '
Dim connectionString, companyToken
companyToken = Request("companyToken")
If Session(companyToken & " - db") Then
    connectionString = "...database=" & Session(companyToken & " - db") & "..."
Else
    '  Invalid login, go log in again
End If

This assumes that the token will be the same as the company name, for simplicity. So, for instance, somebody will log in for "company1." Having done so successfully, they get a session variable called "company1 - db", which contains the name of the database (in this case, "company1DBName").

Now, every link they follow should have a query string, like "?companyToken=company1" So, when you are connecting to the database, you take that token and use it to find the right database name: Session("company1" + " - db") = "company1DBName"

If they haven't logged in to that company yet (or if they just make up a company name), they won't have that session variable, and they have to go to the log in screen.

If they log in under two companies at once, you can now handle it because you'll be obtaining the database name on every link.

Make sense?

Whatever you do, do not use the user input to create the connection string directly. In other words, the following is the wrong way:

Dim connectionString
connectionString = "...database=" & Request.Form("companyDB") & "..."

Good luck!

Answer 2

The connection string must be stored some where right? A include .asp I guess.

Add code in that include to examine the company name (stored in the session?) and fixup the connection string accordingly.

Edit:

The issue is you may have code out there in a myriad different ASP pages that assumes the appropriate connection string is available in a variable declared in you db.asp include file (lets call it m_connStr). You don't want to have to modify all these pages in order to meet this new requirement.

Thus you only want to edit the db.asp include file and you just want m_connStr to magically point at the correct DB.

Have your logon page once you know the company set the database name in a Session variable.

Your existing code has the connection string like this:-

m_connStr = "driver={SQL Server};server=11.22.333.444 database=mywebdb", "mylogin", "mypassword"

So we'll use a template:-

m_connStrTemplate = "driver={SQL Server};server=11.22.333.444 database=%db%", "mylogin", "mypassword"

If Session("database") <> "" Then
    m_connStr = Replace(m_connStrTemplate, "%db%", Session("database"))
End If

Note a non-existant database session variable causes the connection string to not be defined hence you can't accidentally connect to a default database.

Now as far as all your ASP pages are concerned it's business as usual but the connection string will vary by session according to the company associated with the logged on user.