How can I connect to a "MS SQL Server Express" database using QIE?

Question

I can not connect to "MS SQL Server Express" or a named instance using QIE.  When I test my connection it fails or times out.

Answer 1

Note: before using MS SQL Server Express, know its limitations.  

MS SQL Server Express by default uses Dynamic TCP Ports, making it difficult to connect to using JDBC.  You will need to configure it to use a static port.  Additionally to connect using JDBC be sure to include the database instance name in the "Host" name field in QIE.  See screen shots and references for more detail.

References: 

http://softwaresalariman.blogspot.com/2007/04/jdbc-to-sql-server-express.html

http://vikdor.blogspot.com/2012/10/connecting-to-sql-server-express.html

https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-2017

https://confluence.atlassian.com/stashkb/connecting-to-named-instances-in-sql-server-from-stash-443548795.html

 

In QIE enter the hostName\instanceName.  Example: cps10\SQLEXPRESS

 

To change the dynamic ports blank out the port number in "TCP Dynamic Ports" and populated a port number in "TCP Port" under the "IPAll" section.

Comments

For Named Instances see the comments on this question:

https://www.qvera.com/kb/index.php/205/does-qie-support-named-instance-database-connections?show=205#q205

Answer 2

How can I access MSSQL Server Configuration Manager on Windows 10 as referenced in the above answer?

The Microsoft Management Console program is not a stand-alone program, SQL Server Configuration Manager does not appear as an application in newer versions of Windows.

On windows 10 open SQL Server Configuration Manager from the start page by typing SQLServerManager13.msc (for SQL Server 2016). For previous versions by replacing the number 13 incrementally with a smaller number until you find your version.

Comments

It is located in C:\Windows\SysWOW64\.  I like to create a shortcut and pin it to the start menu.  This is what i use as the shortcut target: C:\Windows\SysWOW64\SQLServerManager11.msc

https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager?view=sql-server-ver15