IT Nota

  • Home
  • How To
  • .NET
  • WordPress
  • Contact

How to Quickly Query LDAP in Visual Studio SSIS

At times, you need to do a quick query against LDAP, but if your access is quite restricted, sometimes instead of using SQL Server Management Studio, you can also use SSIS Project within Visual Studio to do it.

Steps

  1. Launch Visual Studio.

  2. Create a new project and double-click on Integration Services Project.

    Visual Studio New Integration Services Project

  3. Name your Project name (e.g. ITNotaQueryLDAP), then click Create button.

    Visual Studio Configure New Integration Services Project

  4. For this exercise, we use the default Package.dtsx. If it’s not opened by default, just double click it on the right pane window.

  5. Drag Data Flow Task from SSIS Toolbox window to the Control Flow window.

    Add Data Flow Task to Control Flow in SSIS

  6. Now we want to add a Connection Manager by right-clicking on a blank area under Connection Managers pane and select New ADO.NET Connection….

    Add a new ADO.NET Connection in SSIS Connection Managers

    A smaller window titled Configure ADO.NET Manager will pop up, click on the New button.

    Create a new OLE DB Connection Manager in SSIS

  7. In a Connection Manager window, for the Provider, select OLE DB Provider for Microsoft Directory Services (under .Net Providers for OleDb). Then, click OK.

    OLE DB Provider for Microsoft Directory Services - .Net Providers\SqlClient Data Provider in SSIS

  8. Once you’re back in Connection Manager window, click on the Test Connection button. Make sure you see the Test connection succeeded screen. Click both OK buttons.

    SSIS Test Connection Manager (Successful)

  9. After you clicked OK, you can see the configuration summary of your SSIS connection. Click OK again.

    SSIS Connection Manager Configuration Summary

  10. At this point, you have a Data Connection in your Connection Managers which you can rename to LDAP Data Connection.

    Rename Connection Manager to LDAP Data Connection in SSIS

  11. Next, you can either double-click on the Data Flow Task or click on the Data Flow tab and drag ADO NET Source from the left pane, under Other Sources.

    Double click on the ADO NET Source box, and select the LDAP Data Connection for ADO.NET connection manager:, SQL command for Data access mode: and use the following SQL command as an example under SQL Command text:.

    LDAP://DC=ITNota,DC=Corp,DC=com;(&(objectCategory=Person)
    (SN=Smith));SN,givenName,displayName,
    mailNickName,sAMAccountName,mail,msExchHideFromAddressLists,
    homeMDB;subtree
    

    This query specifically looks for all records for anyone with a last name of “Smith” in the domain. You need to replace the values of the DCs to your own domain and also the search string (Smith) to make it work in your situation. Click Preview button.

    SSIS ADO NET Source Editor with LDAP Query

  12. Once you clicked Preview button, You should see the first 200 rows.

    Preview of LDAP Query in SSIS

That’s all there is to it.

Of course, there’s a caveat of using this as you can only view the first 200 rows of your result. This is not intended to be used as a solution of any deployment other than a workaround when you are limited with what you can access and you need to find a few records from your LDAP/Domain Services.

January 7, 2022 Filed Under: How To Tagged With: ActiveDirectory, ETL, Microsoft, SSIS

How to Fix Failed to Decrypt Protected XML Node Error in SSIS

One morning, you discovered your SSIS package failed to run with the following error message:

Started: 5:00:03 AM
Error: 2021-10-08 05:00:04.62
Code: 0xC0016016
Source: SSIS_Package_ITNota
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This is typically caused by a simple setting that is too often overlooked during deployment.

In general, you do not want to save any connection ID and password in your package so you can inject the value from variables that you store either in a database or if you use a Package Deployment Model, then in an XML saved in the *.dtsConfig file.

So what you want to do before you build the package for PROD deployment, you want to set the package property Protection Level to DontSaveSensitive and you set all the values for your variables in your configuration file.

Steps to Set SSIS Package Property Level

  1. With your SSIS package opened, under the Control Flow tab, on a blank area, right-click your mouse and select Properties or press ALT+ENTER.

  2. Under Properties, go to Security, and look for ProtectionLevel. Check the value. By default, it’s set to EncryptSensitiveWithUserKey.

    SSIS Package Properties under Control Flow Tab

    The ProtectionLevel should be set to DontSaveSensitive.

    SSIS Package Properties Security Protection Level

Save the package and if the configuration is already setup, it’s ready to be deployed.

Further Reading

Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state”

November 1, 2021 Filed Under: How To Tagged With: ETL, Microsoft, SSIS

How to Open Visual Studio Code from command line macOS

When you use Visual Studio Code on Windows, one handy feature is you can always call it from the command line by typing “code .” (without the quotes) in the folder where you want to work on.

But when you try that on macOS Terminal, this is what you get:

zsh: command not found: code

Not to worry, the same feature can be had on macOS but it’s just not installed by default. In order to do so, we need to do a one-time setup from Visual Studio Code.

  1. Launch Visual Studio Code.
  2. Press Cmd ⌘ + Shift ⇧ + P to open the Command Palette.
  3. Type in shell command and select the Shell command: Install ‘code’ command in PATH to install it.

    Visual Studio Code Command Palette - Shell Command code

  4. A screen will pop up stating:

    Code will now prompt with 'osascript' for Administrator privileges to install the shell command.

    Just click OK and authorize it.

Now, the “code .” command works the same way in macOS Terminal as it is on Windows Command Prompt.

If you use VSCodium, you don’t need to do any kinds of setup, it works right away.

Further Reading

How to Use Visual Code as Default Editor in Git
How to Enable Font Ligatures in Visual Studio Code
How to Reassign Shortcut Key for Column Selection in Visual Studio Code
How to Setup Visual Studio Code for Hugo Static Site Generator

Download

Visual Studio Code

August 23, 2021 Filed Under: How To Tagged With: Code Editor, Microsoft, Visual Studio Code

How to Get Application Pool Identity Password With AppCmd

Typically, web application is run using ApplicationPoolIdentity on IIS. However, for various reasons, it’s very common to find large corporations use service accounts to run the application pools.

IIS Application Pools example

Sometimes the challenge comes when you need to move the website to a different server and you’re not given the proper credential to set it up on the new environment. What’s worse, it’s also not uncommon to hear that sometimes not one soul in the company knows the password.

At that point, your options are usually to either have the password reset (a big No-No for a production application), or have a new service account created (also another headache if the service account is tied to other applications or batch jobs for logical grouping).

But there’s a simple trick that’s quite handy to get password from iis application pool by using AppCmd that is commonly used to migrate IIS websites.

Steps

  1. We know that the AppCmd can be used to export the Application Pool information to an XML file. And we can also export only a single AppPool. So in this case, just as an example, we want to export AppPool ITNOTA by typing the following in Command Prompt:

    C:\> %windir%\System32\inetsrv\appcmd list apppool "ITNOTA" /config /xml > C:\Temp\ItnotaAppPool.xml
    

    Export single AppPool by name to an XML file with appcmd.exe

  2. Once that was done, let’s open up ItnotaAppPool.xml file from C:\Temp folder. You would see something similar to the following:

    <?xml version="1.0" encoding="UTF-8"?>
    <appcmd>
      <APPPOOL APPPOOL.NAME="ITNOTA" PipelineMode="Integrated" RuntimeVersion="4.0" state="Started">
        <add name="ITNOTA" autoStart="true" managedRuntimeVersion="v4.0">
          <processModel identityType="SpecificUser"
                        userName="ITNOTA\Admin_Service_Account"
                        password="###MyAccountPassword###"
                        idleTimeoutAction="Suspend" />
          <recycling>
            <periodicRestart>
              <schedule>
              </schedule>
            </periodicRestart>
          </recycling>
          <failure />
          <cpu />
          <environmentVariables>
          </environmentVariables>
        </add>
      </APPPOOL>
    </appcmd>
    

    IIS AppPool export with identity password

  3. If you look at line 7 (highlighted), the password is there in clear text.

    Note: This XML is modified and compacted just to demonstrate where you can find the password ONLY if you use custom identity. If you use ApplicationPoolIdentity, there’s no password associated with it in the XML file.

In theory, you don’t need to look at the password to make the website run. Because importing the XML will transfer all the information exactly the same way as the current setting. So other than updating the IP address, the identity most of the time doesn’t need any modifications. But this trick is always useful, when you also need to retrieve a “lost” password for a service account that’s been there for years.

Further Reading

How to Migrate IIS Websites to a Different Server
Getting Started with AppCmd.exe
Exporting & Importing App Pools and Websites configuration between multiple IIS instances

August 20, 2021 Filed Under: How To Tagged With: IIS, Internet Information Services, Microsoft, Windows Server

How to Migrate IIS Websites to a Different Server

Setting up a website on IIS is very easy to do. However, it can get very tedious when we have to migrate multiple IIS websites to a new server. For a really brand new server, the easiest to migrate all those settings of course is to clone the server and then we can reconfigure the IP addresses for each web instance from IIS on the new server. But what if that option is not possible?

In this post, we’ll discuss two ways to migrate multiple IIS websites from one server to another. Ideally, this is done to an imaged server that does not have any websites set up yet. Otherwise, more tweaking is needed so we do not override the already existing sites on the new server.

Method 1: Using Shared Configuration in IIS

This method is the simplest to implement, but only use this if you have a brand new server. DO NOT use this method when migrating websites in addition to an already existing website (or websites) on the destination server.

Please be aware though if you migrate websites from one version of IIS to a newer one, the process can be messy but doable as long as you’re aware of all the gotchas.

  1. Launch IIS Manager and click on the main server node and click on Shared Configuration.

    IIS Shared Configuration

  2. In Shared Configuration screen, click the Export Configuration link.

    IIS Export Configuration option

  3. In the Export Configuration window, enter a Physical Path where you want the configuration files stored. Make sure whichever path you use, it’s an existing folder. Otherwise, you will get an error message that the path does not exist.

    Enter a strong Encryption keys password then click OK.

    IIS Configuration encryption keys password

  4. Check your export configuration folder, there should be three files created for you:

    1. administration.config
    2. applicationHost.config
    3. configEncKey.key

    IIS migration files export folder

  5. Copy all three files to the new server.

  6. Launch IIS Manager on the new server and click on Share Configuration.

    IIS Shared Configuration

  7. This time, check on the Enable shared configuration option and type in all required information to import the configuration from the first server. Click on Apply after you’re done.

    IIS Enable Shared Configuration Import - Physical path

  8. Optional: Typically you don’t need to user User name and Password, unless if you need your credential to access the Physical path where you store the export files. In this case, you can type in your user name (NTID or a service account) and password.

    IIS Enable Shared Configuration with username and password

  9. After clicking Apply link from either step 7 or step 8 (not both), you will be asked to enter your Encryption Keys Password you entered from step 3.

  10. If you entered the correct Encryption Keys Password, you’ll see a notice such as this:

    IIS Enable Shared Configuration notice

    Your existing IIS encryption keys will be backed up in the current configuration directory on your local computer. To restore these keys at a later time, turn off shared configuration.
    

    Just click the OK button.

  11. Finally, you’ll see a succesful message below:

    IIS Enable Shared Configuration successfully saved

    The changes have been successfully saved.
    
    You must close and reopen IIS Manager for it to recognize the configuration changes that you have made.
    

    Just click OK button again.

  12. Close and reopen IIS Manager to see the changes take effect.

That’s it.

Method 2: Using AppCmd.exe

From the Microsoft’s own documentation:

AppCmd.exe is the single command line tool for managing IIS 7 and above. It exposes all key server management functionality through a set of intuitive management objects that can be manipulated from the command line or from scripts.

One of the things you can do with AppCmd is to search, manipulate, export, and import IIS and ASP.NET configuration and configure application pools. So with that, what are the steps to migrate websites to a different server?

  1. From the server where you want to export all the websites, launch Command Prompt and type the following:

            C:\> %windir%\System32\inetsrv\appcmd list apppool /config /xml > C:\Temp\AppPools.xml
            
  2. This command exports all Application Pools in the server, so if you never delete the default ones, you might run into an issue when importing them to the new server. It’s harmless but at some point, you will need to delete them before you can successfully import it.

  3. Once we completed exporting the Application Pools, we still need to export the website settings separately.

    Back in Command Prompt, type in the following:

            C:\> %windir%\System32\inetsrv\appcmd list site /config /xml > C:\Temp\Websites.xml
            
  4. Copy the two XML files to the new server. As an example here, the assumption here is the files are copied to the same folder on the new server (C:\Temp).

  5. On the new server, launch Command Prompt and type the following:

            C:\> %windir%\System32\inetsrv\appcmd add apppool /in < C:\Temp\AppPools.xml
            
  6. If successful, you can right away import the websites by typing the following still in the Command Prompt:

            C:\> %windir%\System32\inetsrv\appcmd add site /in < C:\Temp\Websites.xml
            

That’s all there is to it. You might still need to do some adjustments in terms of changing the IP addresses etc. But at least the bulk majority of the settings are already transferred and doing it this way will save you a lot of time instead of doing it one by one.

Further Reading

How do you migrate an IIS 7 site to another server?
Exporting & Importing App Pools and Websites configuration between multiple IIS instances
Getting Started with AppCmd.exe
How to Get Application Pool Identity Password With AppCmd

August 18, 2021 Filed Under: How To Tagged With: IIS, Internet Information Services, Microsoft, Windows Server

« Previous Page
Next Page »
Buy me a coffee Support this site
Buy Me a Coffee?

Categories

  • .NET
  • Coding
  • Cybersecurity
  • Database
  • How To
  • Internet
  • Multimedia
  • Photography
  • Programming
  • Resources
  • Review
  • Tips and Tricks
  • Uncategorized
  • Use Case
  • WordPress
  • Writing

Recent Posts

  • How to View Stored Procedure Code in SQL Server
  • How to Find a String in SQL Server Stored Procedures
  • How to Remove Cached Credentials without Rebooting Windows
  • ESP Work Automation: Empowering Enterprises with Streamlined Workflows and Operational Efficiency
  • How to Search for a String in All Tables in a Database

Recent Posts

  • How to View Stored Procedure Code in SQL Server
  • How to Find a String in SQL Server Stored Procedures
  • How to Remove Cached Credentials without Rebooting Windows
  • ESP Work Automation: Empowering Enterprises with Streamlined Workflows and Operational Efficiency
  • How to Search for a String in All Tables in a Database

Tags

.NET .NET Core AdSense ASP.NET Cdonts Dll Classic ASP Code Editor ETL FSharp Genesis Framework Git Google HP Asset Manager HTML5 Hugo IIS Information Security Internet Internet Information Services iOS JAMStack Linux macOS Microsoft Microsoft SQL Server MVC PHP PowerShell Python Simple Mail Transfer Protocol Smtp Server SQL SQL Server SSIS SSMS SSRS Sublime Text Visual Studio Visual Studio Code VPN Windows Windows 8 Windows 10 Windows 2012 Windows Server

Copyright © 2011-2025 IT Nota. All rights reserved. Terms of Use | Privacy Policy | Disclosure