asked on

Connection string to SQL Server Express database on virtual Windows Server 2016...

Hi there.

I have some trouble with the connection string on my Windows 2016 Server with SQL Express server. I'm used to a connection string like this on a standard web hotel:
"Driver={SQL Server};Server=webhotel.com\MSSQL2005;Database=database_name;Uid=db_username;Pwd=db_password"

Since I run web and SQL on the same Windows Server I'm a little confused how to write the connection string to make it work AND to be safe. I'm hesitant to write username and password in the connection string.
I hope some of you with a similar setup can give me a useful hint. My web app is classic asp/aspx/VB.

Best regards

Ulrich

Vitor Montalvão

Are you sure your SQL Express instance is really named webhotel.com\MSSQL2005?

I would expect something like servername\SQLEXPRESS.

ullenulle

ASKER

Hi Vitor.

The webhotel.com\MSSQL2005 is the way it is and isn't an issue. That one works fine. The problem is on my new dedicated server. On the Server Manager I found a connection string like this:

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

Is that what I'm supposed to use as a connection string? I usually referred to the connection string in a separate asp-file. Is it better in an asa.file or something else? Is it necessary at all with Integrated Security?

Best regards

Ulrich

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ullenulle

ASKER

Hi Vitor.

Thank you for clearing that up with the "." at SQLEXPRESS. I added the server name.
Say I would like to keep the Integrated Security. If a user login to the web app with the login that also gives acc ess to the SQL server. Is that possible? Or is it necessary to create a SQL server account and add the credentials to the connection string?

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ullenulle

ASKER

OK. So say I stick to the secure option where a user will enter the credentials for the SQL server. How is the connection string supposed to be written, and where should I store it?

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ullenulle

ASKER

Ok. I tried saving a global.asa in the root of the webfiles:

<SCRIPT LANGUAGE="VBScript" RUNAT="Server">
Sub Application_OnStart
    Dim cnnDem, cnnString
    Set cnnDem = Server.CreateObject("ADODB.Connection")
    cnnDem.CommandTimeout = 60
    cnnString = "Driver={SQL Server};Server=servername\SQLEXPRESS;Database=mcd;Uid=username;Pwd=password"
    Application("conString")=cnnString
    Application("cnn")=cnnString
    Call cnnDem.Open(cnnString)
End Sub
</SCRIPT>

Open in new window

Of course I replaced servername, username and password with the real values. But then no page on my website will show up. I just get the Server 500 error. If I move the global.asa away from the root, then the webfiles works again. What am I doing wrong?

Vitor Montalvão

I'm not an ASP expert so I can't help you with the .asa file.
Let's wait for some ASP expert or you can try to open a new question only in ASP area to get this specific issue helped in that topic area.

ullenulle

ASKER

Ok. Problem was the \SQLEXPRESS in the string. I tried deleting that part, and bingo it worked. :-) Thank you all for your responses.

ullenulle

ASKER

Thank you.