Search code examples
c#sql-serversmo

Why can I not connect to SQL Server using SMO?


I've got an application using SQL Server Management Objects (SMO) which can't seem to connect to the database server, the error being number 26:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

However this seems to be the only thing that can't connect to the database. Connecting to the database using everything below works without a problem:

  • SQL Server Management Studio
  • sqlcmd
  • System.Data.SqlClient.SqlConnection
  • ODBC
  • SQLPS Module in PowerShell

This is the code I'm using, the exception gets thrown when I try to access the database and as far as I can see, all values are what they should be:

Server = new SMO.Server(
    new ServerConnection(
        new SqlConnectionStringBuilder
        {
            DataSource = ServerName,
            InitialCatalog = DatabaseName,
            IntegratedSecurity = true
        }.ConnectionString));

Database = Server.Databases[DatabaseName];

The only thing I've noticed is that when I run sqlcmd -L none of my SQL Server instances show up. I basically expect to see something like this:

Servers:
    SQL2008
    SQL2012
    SQL2014
    SQL2016

But all it says is:

Servers:
        ;UID:Login ID=?;PWD:Password=?;Trusted_Connection:Use Integrated Security=?;*APP:AppName=?;*WSID:WorkStation ID=?;

Solution

  • Solved, just another case of forgetting to RTFM and force of habit.

    Basically, I forgot that the constructor Server(string) takes the instance name rather than the connection string. So it was trying to connect to an instance with the same name as my connection string.

    There's the the other constructor Server(ServerConnection) so what I should have done is either this:

    Server = new SMO.Server(
        new ServerConnection(
            new SqlConnectionStringBuilder
            {
                DataSource = ServerName,
                InitialCatalog = DatabaseName,
                IntegratedSecurity = true
            }));
    

    Or quite simply, this:

    Server = new SMO.Server(ServerName);