Access a Database with Active Directory from a Windows Service in a Container

As companies move to modernize their IT operations they will frequently encounter legacy applications which need to be moved one way or another onto a modern platform such as Azure Service Fabric and/or Containers. Sometimes there is time/budget for a complete rewrite and sometimes not. In this post we'll demonstrate how to deploy a classic Windows Service onto a Windows Container without touching the source code. In the likely event that your Windows Service depends on Active Directory, you will need to Create a Container with Active Directory Support.

In this step-by-step we will:

1. Git the source for a Windows Container project from GitHub
2. build the service
3. install the service on a container
4. create a SQL database table
5. test access to the table from the service

Create a Container with Active Directory Support

Follow the guidance provided at Create a Container with Active Directory Support

- Build / Copy Bits

1. Make a local clone of ContainerSqlAdAccessTest.
2. Adjust connection string values in App.config yo match your database. (Use SQL server IpAddress  instead of SQL server name)
3. Build with VisualStudio
4. Copy project output from dev box to container host c:\temp

- Create database table

1. Create a table on a SQL database as defined below. The name of the SQL database can be anything you wish but the table must be named 'Table1'.
2. Grant  SQL read access to the gMSA account which you created in the Group Managed Service Accounts step.

--- SQL Table1 ---

USE [<Your DB name here>] GO /****** Object: Table [dbo].[Table1] Script Date: 1/12/2017 9:46:54 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Table1]( [Id] [int] NOT NULL, [Data] [varchar](50) NOT NULL, CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

---------------------

On the host console

- Find the container ID
Docker ps

In this case: '4a63ea5d0222' is the container ID

- Copy files to container
Docker cp c:\temp 4a63ea5d0222:c:\temp

Install Service On the container console

- Install service
C:\Windows\Microsoft.NET\Framework\v4.0.30319\installutil.exe "C:\Temp\SqlAccessTestService\SqlAccessTest.exe"

Run Service On the container console

- Open PowerShell
Powershell

- Start
Net start Service1

- Read Eventlog
Get-WinEvent -ProviderName "DbAccessTest" ProviderName: DbAccessTest TimeCreated Id LevelDisplayName Message
----------- -- ---------------- -------
11/15/2016 4:46:27 PM 2 Error DbAccessTest failed : The underlying provider failed on Open. : ...
11/15/2016 4:45:41 PM 1 Information DbAccessTest Service Starting
11/10/2016 9:07:54 PM 2 Error DbAccessTest failed : The underlying provider failed on Open. : ...
11/10/2016 9:07:09 PM 1 Information DbAccessTest Service Starting
11/10/2016 7:00:02 PM 2 Error DbAccessTest failed : The underlying provider failed on Open. : ...
11/10/2016 6:59:14 PM 1 Information DbAccessTest Service Starting
11/10/2016 6:53:49 PM 0 Information Successfully created 'DbAccessTest' event source.

- Read details for a specific event

Get-WinEvent -ProviderName "DbAccessTest" | select -First 1 | fl *

Example Failure Message : SQL Access (markw2016sql1.redmond.corp.microsoft.com) Test Failed : The underlying provider failed on Open. :
The target principal name is incorrect. Cannot generate SSPI context. : Id : 2
Version :
Qualifiers : 0
Level : 2
Task : 1
Opcode :
Keywords : 36028797018963968
RecordId : 156
ProviderName : DbAccessTest
ProviderId :
LogName : Application
ProcessId :
ThreadId :
MachineName : 4a63ea5d0222
UserId :
TimeCreated : 11/15/2016 5:51:37 PM
ActivityId :
RelatedActivityId :
ContainerLog : application
MatchedQueryIds : {}
Bookmark : System.Diagnostics.Eventing.Reader.EventBookmark
LevelDisplayName : Error
OpcodeDisplayName : Info
TaskDisplayName :
KeywordsDisplayNames : {Classic}
Properties : {System.Diagnostics.Eventing.Reader.EventProperty}

Example Success Message : SQL Access (172.17.224.234) Test Passed
Id : 1
Version :
Qualifiers : 0
Level : 4
Task : 1
Opcode :
Keywords : 36028797018963968
RecordId : 161
ProviderName : DbAccessTest
ProviderId :
LogName : Application
ProcessId :
ThreadId :
MachineName : 4a63ea5d0222
UserId :
TimeCreated : 11/15/2016 6:11:16 PM
ActivityId :
RelatedActivityId :
ContainerLog : application
MatchedQueryIds : {}
Bookmark : System.Diagnostics.Eventing.Reader.EventBookmark
LevelDisplayName : Information
OpcodeDisplayName : Info
TaskDisplayName :
KeywordsDisplayNames : {Classic}
Properties : {System.Diagnostics.Eventing.Reader.EventProperty}

Stop Service Net stop Service1

Notes: Use ipaddress for data source name in App.config