I am having a problem with Visual Studio not saving a password when I create a connection in SSIS connection manager.

Philip Lamphear 1 Reputation point
2020-08-25T21:35:57.49+00:00

I am having a problem with Visual Studio not saving a password when I create a connection in SSIS connection manager.
I would like assistance with someone that is an expert in Visual Studio.

Steps to recreate the problem.
Open visual studio 2019.
Create new project
Choose Integration Services Project

Right click Connection Managers and select New Connection Manager
Choose OLEDB
Then New…
I then choose the server name, SQL Server Authentication.
I enter userid and password and click Save my password
Press OK
When I go back into the connection the password is not saved.

Microsoft Visual Studio Community 2019
Version 16.7.2
VisualStudio.16.Release/16.7.2+30413.136
Microsoft .NET Framework
Version 4.8.04084

Installed Version: Community

Microsoft Visual Studio Tools for Applications 2019 00435-60000-00000-AA993
Microsoft Visual Studio Tools for Applications 2019

ADL Tools Service Provider 1.0
This package contains services used by Data Lake tools

ASA Service Provider 1.0

ASP.NET and Web Tools 2019 16.7.532.28833
ASP.NET and Web Tools 2019

ASP.NET Core Razor Language Services 16.1.0.2035807+72d099b977d3a85e65fa3b0614ca8cfc803fef02
Provides languages services for ASP.NET Core Razor.

ASP.NET Web Frameworks and Tools 2019 16.7.532.28833
For additional information, visit https://www.asp.net/

Azure App Service Tools v3.0.0 16.7.532.28833
Azure App Service Tools v3.0.0

Azure Data Lake Node 1.0
This package contains the Data Lake integration nodes for Server Explorer.

Azure Data Lake Tools for Visual Studio 2.4.6000.1
Microsoft Azure Data Lake Tools for Visual Studio

Azure Functions and Web Jobs Tools 16.7.532.28833
Azure Functions and Web Jobs Tools

Azure Stream Analytics Tools for Visual Studio 2.4.6000.1
Microsoft Azure Stream Analytics Tools for Visual Studio

C# Tools 3.7.0-6.20412.3+d3c3a44a4e7ad31cc75c59be0d3df4a19ff33878
C# components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.

Common Azure Tools 1.10
Provides common services for use by Azure Mobile Services and Microsoft Azure Tools.

Fabric.DiagnosticEvents 1.0
Fabric Diagnostic Events

GitHub.VisualStudio 2.11.104.25275
A Visual Studio Extension that brings the GitHub Flow into Visual Studio.

IntelliCode Extension 1.0
IntelliCode Visual Studio Extension Detailed Info

Microsoft Azure HDInsight Azure Node 2.4.6000.1
HDInsight Node under Azure Node

Microsoft Azure Hive Query Language Service 2.4.6000.1
Language service for Hive query

Microsoft Azure Service Fabric Tools for Visual Studio 16.0
Microsoft Azure Service Fabric Tools for Visual Studio

Microsoft Azure Stream Analytics Language Service 2.4.6000.1
Language service for Azure Stream Analytics

Microsoft Azure Stream Analytics Node 1.0
Azure Stream Analytics Node under Azure Node

Microsoft Azure Tools 2.9
Microsoft Azure Tools for Microsoft Visual Studio 2019 - v2.9.30701.1

Microsoft Continuous Delivery Tools for Visual Studio 0.4
Simplifying the configuration of Azure DevOps pipelines from within the Visual Studio IDE.

Microsoft JVM Debugger 1.0
Provides support for connecting the Visual Studio debugger to JDWP compatible Java Virtual Machines

Microsoft Library Manager 2.1.79+ge3567815aa.RR
Install client-side libraries easily to any web project

Microsoft MI-Based Debugger 1.0
Provides support for connecting Visual Studio to MI compatible debuggers

Microsoft Visual Studio Tools for Containers 1.1
Develop, run, validate your ASP.NET Core applications in the target environment. F5 your application directly into a container with debugging, or CTRL + F5 to edit & refresh your app without having to rebuild the container.

NuGet Package Manager 5.7.0
NuGet Package Manager in Visual Studio. For more information about NuGet, visit https://docs.nuget.org/

ProjectServicesPackage Extension 1.0
ProjectServicesPackage Visual Studio Extension Detailed Info

SQL Server Data Tools 16.0.62008.03130
Microsoft SQL Server Data Tools

SQL Server Integration Services 15.0.2000.123
Microsoft SQL Server Integration Services Designer
Version 15.0.2000.123

ToolWindowHostedEditor 1.0
Hosting json editor into a tool window

TypeScript Tools 16.0.20702.2001
TypeScript Tools for Microsoft Visual Studio

Visual Basic Tools 3.7.0-6.20412.3+d3c3a44a4e7ad31cc75c59be0d3df4a19ff33878
Visual Basic components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.

Visual F# Tools 10.10.0.0 for F# 4.7 16.7.0-beta.20361.3+3ef6f0b514198c0bfa6c2c09fefe41a740b024d5
Microsoft Visual F# Tools 10.10.0.0 for F# 4.7

Visual Studio Code Debug Adapter Host Package 1.0
Interop layer for hosting Visual Studio Code debug adapters in Visual Studio

Visual Studio Container Tools Extensions (Preview) 1.0
View, manage, and diagnose containers within Visual Studio.

Visual Studio Tools for Containers 1.0
Visual Studio Tools for Containers

Visual Studio Tools for Kubernetes 1.0
Visual Studio Tools for Kubernetes

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,502 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,721 Reputation points
    2020-08-25T21:51:39.963+00:00

    SSIS packages have ProtectionLevel setting. One of its possible values is 'DontSaveSensitive'. That particular setting is preventing you from saving both user name and password.

    There are multiple ways to solve the issue:

    • If you will start using Windows Authentication instead of SQL Server authentication, the problem will be gone. Because nothing to save and protect.
    • If you select any other setting except 'DontSaveSensitive' on the package level.
    • If you will make an expression for the ConnectionString property.

    20332-ssis-dontsavesensitive.png

    0 comments
  2. Monalv-MSFT 5,896 Reputation points
    2020-08-26T08:19:21.553+00:00

    Hi Philip Lamphear,

    The password for a SQL Login is Sensitive so it will be omitted from a connection string and thus not saved. If you deploy your package to the SSIS catalog you can use an environment variable with the Sensitive property for your password, then map this to the corresponding property in the connection manager. See the following article from Andy Leonard for more details on how to implement this for a SQL Login. If you want to configure the SSIS catalog see Level 18 of this series. You also mentioned that you're relatively new to SSIS, so I'd recommend working through this stairway as well.

    By default SSIS saves and encrypts sensitive data (passwords, etc.) with something called UserKey. This means that no-one else is able to use the connection with saved password other than the person who previously saved the SSIS-package.
    This can be changed through the SSIS package properties -> ProtectionLevel. You should select "EncryptAllWithPassword" and set a password to "PackagePassword" property and save the package. Now when you open the package next time, SSDT will ask you to enter the password. When entered, connection should be working just fine.

    Please refer to the following links:

    Connection Manager in SSIS 2017 Package NOT Saving the Password

    SSIS Connection Manager Not Storing SQL Password

    Best Regards,
    Mona

    If the response is helpful, please click "Accept Answer" and upvote it.

    0 comments