Create an MS SQL Database and Connection Strings for ASP.NET and Classic ASP

Most ASP.NET and Classic ASP sites store their data in Microsoft SQL Server (MS SQL). On FimuroHost Windows Hosting, MS SQL databases run on dedicated database servers, separate from the web servers, and you connect to them with a SQL Server username and password.

This guide covers creating the database and connecting your code to it. To manage tables and data from your PC with SQL Server Management Studio, see Microsoft SQL Server (MS SQL): What It Is and How to Manage It.

Before you start

MS SQL databases are available on Windows Hosting only, not on Linux Cloud Hosting.
Size: each MS SQL database is currently limited to 1 GB. Limits can change, so ask support if you expect to need more.
If you don't see an MS SQL option in StackCP, or it asks you to add one, contact our Sales team. On some plans an MS SQL database is provided as an add-on.

Create the database

Log in to your FimuroHost client area at https://app.fimurohost.com.
Open Services and select your Windows Hosting plan (Starter, Premium or Business).
Click Login to Control Panel. StackCP opens for that website, already signed in.
In the Web Tools (or Databases) section, click MSSQL Databases.
Enter a name for the database and choose a strong password for its user, then create it.
Note the details StackCP shows: database name, username and server name (hostname). The name may have a suffix added automatically; always copy the exact value.

The server name is normally mssql.stackcp.com. If StackCP shows a different hostname for your database, use that one everywhere below.

The four values every connection needs

Value	Connection string keyword	Example
Server	`Server` or `Data Source`	`mssql.stackcp.com`
Database	`Database` or `Initial Catalog`	`shopdb-3a1f`
Username	`User Id`	`shopdb-3a1f`
Password	`Password`	the password you set

Shared hosting uses SQL Server authentication. Remove Integrated Security=True or Trusted_Connection=True from connection strings copied from your development PC; Windows authentication won't work here.

ASP.NET (.NET Framework) and ADO.NET

Put the connection string in web.config:

<configuration>
  <connectionStrings>
    <add name="DefaultConnection"
         connectionString="Server=mssql.stackcp.com;Database=shopdb-3a1f;User Id=shopdb-3a1f;Password=YourStrongPassword;MultipleActiveResultSets=True;"
         providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

Read it in C# and always use parameters for values that come from users:

using System.Configuration;
using System.Data.SqlClient;

var cs = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

using (var conn = new SqlConnection(cs))
using (var cmd = new SqlCommand(
    "SELECT Id, Name, Price FROM Products WHERE CategoryId = @cat", conn))
{
    cmd.Parameters.AddWithValue("@cat", 3);
    conn.Open();
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            var name = reader.GetString(1);
            // ...
        }
    }
}

Entity Framework 6 Code First uses the same connectionStrings entry: pass its name to your DbContext constructor (base("name=DefaultConnection")). For an EF Database First model (.edmx), keep the metadata=res://… part of your existing connection string and only change the server, database, user and password inside provider connection string.

Using Microsoft.Data.SqlClient

The newer Microsoft.Data.SqlClient package (version 4 and later) encrypts connections by default and checks the server certificate. If you see “The certificate chain was issued by an authority that is not trusted”, add TrustServerCertificate=True; to the connection string.

Classic ASP (ADO)

Keep the connection code in one include file, for example /includes/db.asp. Give it the .asp extension, not .inc: IIS runs .asp files, but a .inc file could be downloaded as plain text, revealing your password.

<%
' /includes/db.asp
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=mssql.stackcp.com;" & _
          "Initial Catalog=shopdb-3a1f;User ID=shopdb-3a1f;Password=YourStrongPassword;"
%>

Use it from a page with a parameterised query:

<!--#include virtual="/includes/db.asp"-->
<%
Dim cmd, rs
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT Name FROM Products WHERE CategoryId = ?"
cmd.Parameters.Append cmd.CreateParameter("cat", 3, 1, , Request.QueryString("cat"))  ' 3 = adInteger, 1 = adParamInput
Set rs = cmd.Execute
Do While Not rs.EOF
  Response.Write Server.HTMLEncode(rs("Name")) & "<br>"
  rs.MoveNext
Loop
rs.Close : conn.Close
Set rs = Nothing : Set cmd = Nothing : Set conn = Nothing
%>

Never build SQL by joining strings with Request values. That is how most Classic ASP sites get hacked through SQL injection.

Test the connection from your PC

Connecting with SQL Server Management Studio (server mssql.stackcp.com, SQL Server Authentication, tick Trust server certificate if asked) is the quickest way to prove the username and password are right before you debug your code. Run SELECT @@VERSION; to see which SQL Server version you are on.

Common problems

Login failed for user ‘…’. Wrong username or password, or the database name has a suffix you left out. Copy the values from StackCP again; reset the password there if needed.
A network-related or instance-specific error… (provider: Named Pipes Provider, error: 40). The server name is wrong, often .\SQLEXPRESS or (localdb)\MSSQLLocalDB left over from development.
Cannot open database requested by the login. The Database value doesn't match your database name exactly.
The database is full / “Could not allocate space”. You have reached the size limit. Clear out old data (logs, sessions) or ask support about your options.
500.19 after adding the connection string. An & in the password breaks the XML. Write it as & in web.config, or choose a password without it.

Need help?

If something doesn't work as described, open a support ticket from your client area or message us on WhatsApp at 01818160926. Never send us your database password in a ticket; tell us the database name and the exact error instead.

FimuroHost Team

Technical Writer