Test the connection to a SQL Server database

There are many cases where we want to verify that the connection string used by an application is valid (in production of course 😃). There are several methods for this. The two most simple in my opinion are:

  1. Create a new file with the .udl extension (Universal Data Link)
  2. Open the created file ⇒ A window to enter the login information opens
  3. Fill in the connection information
  4. Click on the "Test connection" button

If this option is not possible (for example with a Windows Server Core Edition), it is still possible to use PowerShell:

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=(local);Database=Sample;Integrated Security=True;"
$conn.Open()
$conn.Close()

In case of error, a complete error message is displayed:

IIS App Pool & SQL Server

This error occurs when the account running the website can not connect to the database. Unfortunately the error message, although accurate, is not as explicit as it could be. The account displayed corresponds to the name of the machine followed by the "$" sign, but it is not this account that must be authorized in SQL Server. In fact we must add the account of the AppPool:

First thing: look at the pool application used by the site (here "DefaultAppPool"):

The account to add in SQL Server is "IIS AppPool<Application Pool>" (here "IIS AppPool\DefaultAppPool"):

CodeFluent Entities

CodeFluent Entities provides several ways to define a connection string (see documentation). This makes it easy to work with multiple people on the same project (unlike other competing solutions). For example, the configuration file might look like:

<connectionStrings>
    <add name="DEV01" connectionString="Application Name=Sample;server=(localdb)\v11.0;database=Sample;Integrated Security=true" />
    <add name="DEV02" connectionString="Application Name=Sample;server=(local)\SQL2012;database=Sample;Integrated Security=true" />
    <add name="DEV03" connectionString="Application Name=Sample;server=(local)\SQL208R2;database=Sample;Integrated Security=true" />
    </connectionStrings>

    <Sample connectionString="{%ComputerName%}" />

The connection string used (DEV01, DEV02 or DEV03) depends on the environment variable %ComputerName% (name of the current machine). It is possible via PowerShell to know the connection string used during the execution:

# configuration
$dir = "<path to the application>"
$dll = "$dir\bin\<dll name>.dll"
$appConfigPath = "$dir\web.config"
#Load dll
Add-Type -Path "C:\Program Files (x86)\SoftFluent\CodeFluent\Modeler\CodeFluent.Runtime.dll"
Add-Type -Path "$dll"
# Load app.config file
[System.AppDomain]::CurrentDomain.SetData("APP_CONFIG_FILE", "$appConfigPath")
# Get the connection string
[CodeFluent.Runtime.CodeFluentContext]::Get([<Default Namespace>.Constants]::<Store name>).Persistence.ConnectionString
<section name="Sample" type="CodeFluent.Runtime.CodeFluentConfigurationSectionHandler, CodeFluent.Runtime, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1bb6d7cccf1045ec" />
Enjoy this blog? Buy Me A Coffee Donate with PayPal

Leave a reply