Connecting to SQL express 2019 server

Question

Connecting to SQL express 2019 server

waltthrombey

Newbie

Points: 2
More actions
October 27, 2025 at 10:08 am

#4678079

Hello,
Our old server is on its last legs, and I'm transitioning to a new one. It's been a decade since I set up SQL, and the app we're using relies on SQL Express 2019. I'm trying to install it on Windows Server 2025, but the app is outdated and isn't compatible with newer SQL versions.
I downloaded the latest installer for SQL Express 2019 and installed SQL Management Studio 21.
I attempted to connect to the database directly from the SQL server, and there are no firewall issues. However, I couldn't set up an ODBC DSN. I made sure to enable TCP/IP and named pipes in the server configuration and rebooted.
Since I haven't set up a SQL server since 2014, I've forgotten some details, and things have changed. Any idea what I need to do to establish a connection, considering it's not a firewall issue?

Viewing 2 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323579 More actions · Answer 1

by default, SQL Express has tcp disabled, and you have to explicitly enable that in the SQL Configuration Manager, did you do that yet? that's a difference between what we think of as standard or enterprise, where external connections are assumed, and Express, where external are not so typical.

After that, the local firewall on the machine might need to be configured to allow access. my powershell script for that is below.

eg_enable_tcpip_sqlserver01_01[1]

#Requires -RunAsAdministrator
###################################################################################################
## Purpose: Finds This Servers SQL instnaces, and whatever Port it listens to in IPAll 
###################################################################################################
Clear-Host
$Error.Clear()
#region Prerequisites
###################################################################################################
## Prerequisites (Load Modules and installs)
###################################################################################################
#endregion

#region Load References
###################################################################################################
##Load References
###################################################################################################
#endregion

#region Local Functions
###################################################################################################
## Local Functions
###################################################################################################
function die {Write-Error "Error: $($args[0])";exit 1}
function verifySuccess {if (!$?) {        die "$($args[0])"    }}
function Get-SQLInstancesPort {

    param ([string]$Server)

    [system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|Out-Null
    [system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")|Out-Null
    $mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $Server
    $Instances = $mc.ServerInstances

    foreach ($Instance in $Instances) {
        $port = @{Name = "Port"; Expression = {$_.ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value}}
        $Parent = @{Name = "Parent"; Expression = {$_.Parent.Name}}
        $Instance|Select $Parent, Name, $Port
    }
}
#endregion

#region Variables
###################################################################################################
## Variables
###################################################################################################
#region Automatic Logging - Start
$LogFolder = [System.IO.Path]::GetDirectoryName($psise.CurrentFile.FullPath)
$JustTheFileName = [System.IO.Path]::GetFileName($psise.CurrentFile.FullPath)
$LogFile = [System.IO.Path]::Combine($LogFolder,$JustTheFileName.Replace(".ps1","") +"_"+ (Get-Date).ToString("yyyy-MM-dd") + ".txt")
Start-Transcript -Path $LogFile
$StartTime = Get-Date
#endregion
#endregion

#region Houskeeping
###################################################################################################
## Housekeeping
###################################################################################################

#endregion

#region The Work
###################################################################################################
## The Work
###################################################################################################

$DiscoveryItems = Get-SQLInstancesPort

foreach ($Item in $DiscoveryItems)
{
    $AllRules = Get-NetFirewallRule -DisplayName "SwQL*"
    ##Specific Ports for this Specific Instance
    if ($AllRules -eq $null -or( -not $AllRules.DisplayName.Contains(("SQL Server " + $Item.Name))) )
    {

        $ports =  ($Item.Port).Split(",") #this might be a value like "1433,42151", so we need to split it to a string array
        New-NetFirewallRule -DisplayName ("SQL Server " + $Item.Name) -Direction Inbound –Protocol TCP –LocalPort $ports  -Action allow
    }
    ##General Port For DAC
    if ($AllRules -eq $null -or( -not $AllRules.DisplayName.Contains("SQL Dedicated Admin Connection")) )
    {
        New-NetFirewallRule -DisplayName "SQL Dedicated Admin Connection" -Direction Inbound –Protocol TCP –LocalPort 1434 -Action allow
    }
    ##General Port For SQL Browser
    if ($AllRules -eq $null -or(-not $AllRules.DisplayName.Contains("SQL Browser")) )
    {
        New-NetFirewallRule -DisplayName "SQL Browser" -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow
    }
    ##General Port For SQL AAG/
    if ($AllRules -eq $null -or(-not $AllRules.DisplayName.Contains("SQL AlwaysOn Availability Group EndPoint")) )
    {
        New-NetFirewallRule -DisplayName "SQL AlwaysOn Availability Group EndPoint" -Direction Inbound –Protocol UDP –LocalPort 5022 -Action allow
    }
    ##General Port For SQL Service Broker
    if ($AllRules -eq $null -or(-not $AllRules.DisplayName.Contains("SQL Server Service Broker EndPoint"))) 
    {
        New-NetFirewallRule -DisplayName "SQL Server Service Broker EndPoint" -Direction Inbound –Protocol UDP –LocalPort 4022 -Action allow
    }

}

#region Automatic Logging - End
##tail of all Work: review log
$EndTime = (Get-Date)
$ts = New-TimeSpan -Start $StartTime -End $EndTime
$MyElapsedTime ="Elapsed Time hh:mm:ss:: " +  ($ts.Hours).ToString("00") + ':'+ ($ts.Minutes).ToString("00") + ':' + ($ts.Seconds).ToString("00")
Write-Host $MyElapsedTime -ForegroundColor Yellow
Stop-Transcript
Invoke-Item $LogFile
#endregion
#endregion

#region TheOutput
###################################################################################################
## The Output
###################################################################################################

#endregion
verifySuccess $MyInvocation.MyCommand.Name + " Failed"

This reply was modified 2 hours, 6 minutes ago by Lowell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!