"Login Failed" when using SMO to create SQL script with the ScriptData option

Question

I am attempting to export and import a database schema and the data within. I'm using SMO (Server Management Objects) to do so, but while the export of the schema is working just fine, setting ScriptData to true in the scripting options will produce an exception.

Server server = new Server(new ServerConnection(sqlCon));
Database database = server.Databases["SpectroGrass"];
ScriptingOptions options = new ScriptingOptions();
options.ScriptSchema = true;
options.ScriptData = false;
options.ScriptDrops = false;

string scriptData = String.Join("\r\n", database.Tables[tableName].EnumScript(options).ToList());

Will produce a schema and is working.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE TABLE [SpectroGrassAdmin].[Treatment]
(
    [TRM_ID] [int] NOT NULL,
    [TRM_Name] [varchar](150) COLLATE Latin1_General_CI_AS NULL,
    [TRM_Crop] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

But using the ScriptData option ...

options.ScriptSchema = false;
options.ScriptData = true;
options.ScriptDrops = false;

string scriptData = String.Join("\r\n", database.Tables[tableName].EnumScript(options).ToList());

will generate an exception.

Login failed for user 'SpectroGrassAdmin'.

I'm not sure why I'm getting this specific error. I can only assume the problem lies somewhere else, because logging into the server and connecting to the database is working fine if I only want the schema exported.

Am I missing some other options to be able to create a script for the table data?

Answer 1

Try to simplify it as much as possible. The peculiarity of smo is that by default it points/connects to the default sql instance.

Does it happen that you have a default instance on the machine you are running your app and you are trying to smo to another instance? (either secondary on your machine or a remote instance on another machine).

The following was taken from an example of smo using ServerConnection, which enables the connection information to be reused.

//  https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/create-program/connecting-to-an-instance-of-sql-server?view=sql-server-ver15#connecting-to-an-instance-of-sql-server-by-using-sql-server-authentication-in-visual-c

//// compile with:   
// /r:Microsoft.SqlServer.Smo.dll  
// /r:Microsoft.SqlServer.ConnectionInfo.dll  
// /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll   

using System;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SMOConsoleApp
{
    class Program
    {
        static void Main()
        {

            // For remote connection, remote server name / ServerInstance needs to be specified  
            ServerConnection srvConn2 = new ServerConnection("machinename"/* <--default sql instance on machinename*/);  // or (@"machinename\sqlinstance") for named instances
            srvConn2.LoginSecure = false;
            srvConn2.Login = "sql_login_goes_here";
            srvConn2.Password = "password_goes_here";
            Server srv3 = new Server(srvConn2);

            Console.WriteLine("servername:{0} ---- version:{1}", srv3.Name, srv3.Information.Version);   // connection is established  
            Console.WriteLine(srv3.ConnectionContext); //check connection context

            ScriptingOptions scriptoptions = new ScriptingOptions();
            scriptoptions.ScriptSchema = true;
            scriptoptions.ScriptData = true;
            scriptoptions.ScriptDrops = false;

            Database mydb = srv3.Databases["master"];
            /*
                --execute this in ssms, in the master db
                select *
                into dbo.testsysobjects
                from sys.objects
            */
            Table mytable = mydb.Tables["testsysobjects", "dbo"];

            Console.WriteLine("database: {0} ---- table: {1} ---- rowcount: {2}", mytable.Parent.Name, mytable.Name, mytable.RowCount); //check table

            string scriptData = String.Join("\r\n", mytable.EnumScript(scriptoptions).ToList());

            Console.Write(scriptData);

            srvConn2.Disconnect();

        }
    }
}