How to Test a Persistent off core SQL Server Connection

Version 16

    Verified Product Versions

    Endpoint Manager 2016.x



    The purpose of this article is to connect to an off-core SQL Server without using LANDesk technology to ensure database connectivity. A connection will be created from the LDMS core server to the SQL server using Microsoft’s SQLCMD.exe tool. SQL activity monitor will be used to monitor the connection watching for the connection to drop. If the connection drops it can be concluded that there is an issue with network connectivity between SQL and the Core that will need to be resolved before further troubleshooting can be done.



    Tools Needed


    • Microsoft SQL Server Command Line Query Utility SQL 2008


    Microsoft SQL Server 2008 Command Line Utilities


    The SQLCMD utility allows users to connect to, send Transact-SQL batches from, and output rowset information from SQL Server 7.0, SQL Server 2000, SQL Server 2005, and SQL Server 2008 instances. The bcp utility bulk copies data between an instance of Microsoft SQL Server 2008 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.


    Note: This component requires both Windows Installer 4.5 and Microsoft SQL Server Native Client


    Microsoft SQL Server 2008 Feature Pack, October 2008


    Microsoft SQL Server 2008 Command Line Utilities

    X86 Package
    (SqlCmdLnUtils.msi) - 7234 KB
    X64 Package (SqlCmdLnUtils.msi) - 12212 KB
    IA64 Package(SqlCmdLnUtils.msi) - 16515 KB


    Microsoft SQL Server 2008 Native Client

    X86 Package
    (sqlncli.msi) - 4549 KB
    X64 Package (sqlncli.msi) - 7963 KB
    IA64 Package (sqlncli.msi) - 11112 KB


    Steps for testing


    Core Side


    1. Install SqlCmdln Utility on the core server.
    2. Open a command prompt.
    3. Change the directory to \Program Files\Microsoft SQL Server\100\Tools\Binn
    4. Enter the following command string: sqlcmd.exe -S SQLServerName -d DatabaseName -U Username -P Password (example: sqlcmd.exe -S SQL005 -d LDDB -U sa -P Password1 )

    If the connection is successful you will see a 1> on your screen.



    For a full list of switches for SqlCmd refer to this MSDN article.


    SQL Server Side


    1. Open SQL Management Studio and connect to the SQL server in the Object Explorer window.
    2. Expand the Management tree then open the activity monitor. Locate the connection opened by the SQLCMD application.


    At this point, you will want to view the activity monitor on the SQL server to see if the connection has stayed up. Viewing the activity monitor at the end of the day or the next day will determine if there have been any disconnects. SQL command line utility will not reconnect if there is a drop either by SQL or network. The application name(SQLCMD) is listed in the application column of Activity Monitor so it is easy to identify the sqlcmd connection.