SQL Server Management Objects (SMO) - Login failed for user.Error on production server

Question

I have written a code which take backup of database (Generate Sql script) on button click, This code is working fine on localhost, However when i deploy this to production i get the following error.

Login failed for user 'XXXXXX\IWPD_1257(XXXXX)'

A part of Stack Trace is

[ConnectionFailureException: Failed to connect to server ..]
   Microsoft.SqlServer.Management.Smo.DatabaseCollection.get_Item(String name) +370
   LeadWeb.Backup.scriptTables() +232
   LeadWeb.Backup.btnsubmit_OnClick(Object sender, EventArgs e) +5
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9628114
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +103
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

Here is my code

public void scriptTables()
        {
            try
            {
                string folder = HttpContext.Current.Server.MapPath("~/Excels/data.sql");// I have this file 
                System.IO.File.WriteAllText(folder, string.Empty);
                Server myServer = new Server(ConfigurationManager.AppSettings["server"]);
                Database CMSDB = myServer.Databases[ConfigurationManager.AppSettings["Database"]];

                Scripter script = new Scripter(myServer);
                ScriptingOptions so = new ScriptingOptions();
                so.AnsiPadding = true;
                so.IncludeHeaders = true;
                so.Default = true;
                so.DriForeignKeys = true;
                so.DriPrimaryKey = true;
                so.DriUniqueKeys = true;
                so.ScriptData = true;
                so.ScriptSchema = true;
                so.ScriptDrops = false;


                StringBuilder ss = new StringBuilder();

                foreach (Table table in CMSDB.Tables)
                {
                    string tables = table.Name;
                    string filename = folder;

                    so.FileName = filename;
                    so.AppendToFile = true;
                    CMSDB.Tables[tables].EnumScript(so);
                }
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ClearHeaders();
                HttpContext.Current.Response.ClearContent();
                HttpContext.Current.Response.AddHeader("Content-Disposition",
                    "attachment; filename=" + "LeadBackup" + DateTime.Now.Date.ToString("d") + ".sql");
                HttpContext.Current.Response.AddHeader("Content-Length", folder.Length.ToString());
                HttpContext.Current.Response.ContentType = "text/plain";
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.TransmitFile(folder);
                HttpContext.Current.Response.End();

            }
            catch (Exception e)
            {

                Response.Write(e.Message);
            }

Webconfig values

<add key="Server" value="." /> // changes according to server <add key="Database" value="Lead" /> // changes according to Database

Any help or guidance.?

Answer 1

My guess is that your database connection string is using integrated security, and XXXXXX\IWPD_1257(XXXXX) is not configured to access the database server and/or the database.

In SSMS, go to security > logins and identify the windows groups that have access to the server. In active directory, add XXXXXX\IWPD_1257(XXXXX) to one of those groups.

Alternately, you can directly add XXXXXX\IWPD_1257(XXXXX) as a login.

Regardless of the approach, make sure the group or user is also added to the database security and granted access to the roles required to query from the tables you need.