none
SQL Connection string for service account in a C# solution RRS feed

  • Question

  • Hi All,

    I am very new in coding the C# solutions, I have a c# solution which will connect to the TFS (Team foundation server) download the files and then deploy them to the SQL ETL server by connecting it to the ETL server. Since, I am executing it on my local machine I am using windows authentication SQL connection string where Integrated Security=SSPI. But now, we want to use the same solution by using the SQL server authentication from the service account where we will be given the loginID and password. So, what should be changed in my code for connection string if it is a service account with Login and Password details.

    using System;
    using System.Data.SqlClient;
    using System.IO;
    using System.Linq;
    using System.Windows.Forms;
    using Microsoft.SqlServer.Management.IntegrationServices;
    using Microsoft.TeamFoundation.Client;
    using Microsoft.TeamFoundation.VersionControl.Client;
    using Microsoft.TeamFoundation.VersionControl.Common;
    
    namespace SSIS_Deployment_3
    {
        public partial class Form1 : Form
        {
            private CatalogFolder folder;
    
            public void Form2()
            {
    string teamProjectCollectionUrl = "your TFS url";
                string TFSserverPath = ""your path;
     string SSISProjectFilePath = System.Configuration.ConfigurationManager.AppSettings[@"SSISProjectFilePath"];
                //string localPath = @"Z:\SSIS_Sample\Files";
                TfsTeamProjectCollection teamProjectCollection = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(new Uri(teamProjectCollectionUrl));
                VersionControlServer versionControlServer = teamProjectCollection.GetService<VersionControlServer>();
    
                foreach (Item item in versionControlServer.GetItems(TFSserverPath, VersionSpec.Latest, RecursionType.Full, DeletedState.NonDeleted, ItemType.Any, true).Items)
                {
                    string target = Path.Combine(SSISProjectFilePath, item.ServerItem.Substring(2));
    
                    if (item.ItemType == ItemType.Folder && !Directory.Exists(target))
                    {
                        Directory.CreateDirectory(target);
                    }
                    else if (item.ItemType == ItemType.File)
                    {
                        item.DownloadFile(target);
                    }
                }
    
                // Declare and assign values to Variables
                string SSISServerName = System.Configuration.ConfigurationManager.AppSettings["SSISServerName"];
    			
    string SSISFolderName = "";
                string SSIS_Sub_Folder = System.Configuration.ConfigurationManager.AppSettings["SSIS_Sub_Folder"];
                //string SSIS_Sub_Folder = "CPM05";
    
    
                // Creating a connection
                string sqlConnectionString = "Data Source=" + SSISServerName +
                ";Initial Catalog=SSISDB;Integrated Security=SSPI;User ID=arachama;Password=Pandu123!3";
                // "Data Source=ETLET0083;Initial Catalog=SSISDB;Integrated Security=SSPI;";
                SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
    
                // Creating a SSIS object
                Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices integrationServices = new Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices(sqlConnection);
                Catalog catalog;
    
    {
                    // Creating a object for existing SSIS Catalog
                    catalog = integrationServices.Catalogs["SSISDB"];
    //CatalogFolder folder;
    
                    string[] fileList = Directory.GetFiles(SSISProjectFilePath, "*.ispac", SearchOption.AllDirectories);
                    foreach (string file in fileList)
                    {
                        Console.WriteLine("ISPAC File Name" + file);
                        SSISFolderName = Path.GetFileNameWithoutExtension(file);
    
                        // Creating a object for SSIS folder
                        //folder =  CatalogFolder(catalog, SSISFolderName, SSISFolderName);
                        folder = catalog.Folders[SSIS_Sub_Folder];
                        //folder.Create();
                        Console.WriteLine("SSIS Catalog folder " + SSISFolderName + " has been created !!!:");
    
                        Console.WriteLine("Deploying " + SSISFolderName + " project.");
    
                        byte[] projectFile = File.ReadAllBytes(file);
                        folder.DeployProject(SSISFolderName, projectFile);
                        Console.WriteLine("SSIS Project has been successfully deployed !");
                    }
                    //this.Close();
                }
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                this.Close();
                
            }
    
            }
        }
    

    Can someone please help me in this?


    Aparanjit

    Tuesday, October 29, 2019 9:35 PM

Answers

All replies

  • If you want SQL login, you should not have Integrated Security=SSPI in the connection string.

    But I don't like putting passwords in the code. What service is this going to run from?

    And with TFS, I believe that integrated security is the only option.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 29, 2019 10:40 PM
  • I mean I need the exact connection string we use for SQL Login, they will be providing use with the functional user account information which will have Login and password details. All I am trying to do here is use this code to make an exe application and run from the scheduler in all the lower environments using the configuration file for Connection string details. 

    But in Production, someone from DBA team will be changing the configuration file to match the SQL login credentials of PROD.

    Since, I am using connection string as below currently;

    " Data Source=ETLET0083;Initial Catalog=SSISDB;Integrated Security=SSPI; "

    What would I change this to, if they provide me with the SQL login details with logiID and password?


    Aparanjit

    Tuesday, October 29, 2019 10:50 PM
  • What would I change this to, if they provide me with the SQL login details with logiID and password?

    Use

    Initial Catalog=SSISDB;Integrated Security=false;User ID=arachama;Password=Pandu123!3

    See https://www.connectionstrings.com/all-sql-server-connection-string-keywords/


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Rahul 11 Wednesday, October 30, 2019 2:31 PM
    Wednesday, October 30, 2019 9:31 AM