[{"_1":2,"_160":-5,"_161":-5},"loaderData",{"_3":4,"_17":18,"_25":26,"_139":140},"root",{"_5":-5,"_6":-7,"_7":8,"_9":-7,"_10":11,"_12":-7,"_13":14,"_15":16},"currentUser","primaryEmail","siteVerification","6LeIxAcTAAAAAJcZVRqyHh71UMIEGNQ_MXjiZKhI","toast","enableDev",false,"incidentStatus","theme","system","contrast","normal","routes/_layout",{"_19":-5,"_20":21,"_22":21,"_23":11,"_24":21},"username","isAskGordonEnabled",true,"isUsingNewTemplate","showMinimalLanding","showHoneypotTrap","routes/_layout.r.$namespace.$reponame",{"_27":28,"_29":-5,"_30":11,"_31":11,"_32":33,"_34":35,"_36":-5,"_37":11,"_38":39,"_88":-5,"_89":90,"_128":-5,"_129":11,"_130":21,"_131":11,"_132":-5,"_133":11,"_134":-5,"_135":136,"_137":91,"_138":-7},"canonicalUrl","https://hub.docker.com/r/kedubuis/new/tags","mcpServer","canSeeDockerfile","canSeeAutobuilds","namespace","kedubuis","reponame","new","cloudBuildSource","isAutobuildsAvailable","repo",{"_40":41,"_42":43,"_45":46,"_47":48,"_49":11,"_50":51,"_52":53,"_54":55,"_57":35,"_32":33,"_58":59,"_60":44,"_61":62,"_63":64,"_65":66,"_67":-5,"_68":62,"_69":70,"_71":11,"_72":33,"_73":74,"_79":11,"_80":81,"_85":33,"_86":87},"categories",[],"content_types",[44],"image","date_registered","2020-05-14T20:23:51.412593Z","description","","is_private","last_updated","2020-05-18T03:34:10.452351Z","last_modified","2024-10-16T13:48:34.145251Z","media_types",[56],"application/vnd.docker.container.image.v1+json","name","pull_count",427,"repository_type","star_count",0,"status",1,"status_description","active","source","collaborator_count","full_description","# Demo using AAD Pod Identity in AKS POD to connect to Azure SQL\n\n\n## Build image\nDockerfile is utilizing Multi Stage builds and is installing [MSSQL ODBC Driver 17](https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017) for Debian9 in the image.\nStarting from version msodbcsql17_17.5.2.1 driver supports using ManagedIdentity with AlwaysEncrypted AzureKeyvault Keys Provider scenario.\n\n```\ndocker build -t cloudquery .\ndocker run -d -p 8080:80 --name cloudquery cloudquery\ndocker exec -it cloudquery bash\n\ndocker login .azurecr.io\ndocker tag cloudquery .azurecr.io/cloudquery\ndocker push .azurecr.io/cloudquery\n```\n\nor using ACR\n\n```\naz login\naz acr build --registry --image cloudquery:latest . \n```\n\n### Deploy the Application\n\nDeploy the app\n```\nkubectl apply -f manifests/k8-manifest.yaml\n```\n\n\n# Test the application\n\nNavigate to LoadBalancer IP to get to the application\n```\nkubectl get svc cloudquery-service\n```\n\nClick New and type database name prepended by `#`, application will read servername, user and password from mapped file paths when encountering `#` at the start of db name.\n\n![docs](./docs/cloudquery-add.png)\n\nuse simple query like `select 1` to verify connection\n\n# Use Managed Identity for Connection\n## install AAD Pod identity\n\nODBC driver (as well as JDBC and .NET SQL Client) suppports Azure Managed Identity to connect to Azure SQL.\n\nTo assign MSI to application running in K8S use [AAD Pod Identity](https://github.com/Azure/aad-pod-identity) project.\n\n1. Install AAD Pod identity\n\n```\nkubectl apply -f https://raw.githubusercontent.com/Azure/aad-pod-identity/master/deploy/infra/deployment-rbac.yaml\n```\n2. Create Managed Identity and record the output Json\n```\n az identity create -g -n -o json\n{\n \"clientId\": \"e8e422cc-2bb7-4d06-ba43-cff38b07a026\",\n \"clientSecretUrl\": \"https://control-canadacentral.identity.azure.net/subscriptions/xxx\",\n \"id\": \"/subscriptions/xxx/resourcegroups/aks-tests/providers/Microsoft.ManagedIdentity/userAssignedIdentities/odbcidentity\",\n \"location\": \"canadacentral\",\n \"name\": \"odbcidentity\",\n \"principalId\": \"xxx\",\n \"resourceGroup\": \"xxx\",\n \"tags\": {},\n \"tenantId\": \"xxx\",\n \"type\": \"Microsoft.ManagedIdentity/userAssignedIdentities\"\n}\n```\n\n3. find what service Principal is used by AKS (or MSI if used in provisioing) and grand it access to manage Pod Managed Identity\n\n```\naz aks show -g -n | grep clientId\n```\n\n- grant AKS client access to MSI\n\n```\naz role assignment create --role \"Managed Identity Operator\" --assignee --scope /subscriptions//resourcegroups//providers/Microsoft.ManagedIdentity/userAssignedIdentities/\n```\n\n4. For simplicity Grant to Pod Identity Contributor role (should later grant only required access to sql and keyvault)\n\n```\naz role assignment create --role \"Contributor\" --assignee --scope /subscriptions//\n```\n\n5. grant Pod Identity MSI access to SQL Database ( typically add to AD group, here for simplicity admin)\n```\naz sql server ad-admin create --resource-group --server-name --display-name adminmsi --object-id \n```\n\n6. Create Azure Pod Identity and Binding in K8S for the pods to use\n```\n kubectl apply -f manifests/aadpodidentity.yaml\n```\n- Pod identity Manifests describing identity to bind to applications\n\n```yaml\napiVersion: \"aadpodidentity.k8s.io/v1\"\nkind: AzureIdentity\nmetadata:\n name: aksodbcpodidentity\nspec:\n type: 0\n ResourceID: /subscriptions//resourcegroups//providers/Microsoft.ManagedIdentity/userAssignedIdentities/\n ClientID: \n--- \napiVersion: \"aadpodidentity.k8s.io/v1\"\nkind: AzureIdentityBinding\nmetadata:\n name: aksodbcpodidentity-binding\nspec:\n AzureIdentity: \"aksodbcpodidentity\"\n Selector: \"odbcidentity\"\n```\n\nNow any Pod having label `aadpodidbinding: odbcidentity` will be bind to created MSI and will use it for MSI enabled calls\n\n7. Update Kubernetes Manifest to add AAD Pod label and enable MSI for connection string, as example in updated to have `aadpodidbinding` label:\n\n```yaml\ntemplate:\n metadata:\n labels:\n app: cloudquery-web\n aadpodidbinding: odbcidentity\n```\n8. Update Environment variable for Pods to use MSI enabled connection string in code, in `manifests/k8-manifest.yaml`:\n\n```yaml\n image: acraccess.azurecr.io/cloudquery:msi \n imagePullPolicy: Always\n env:\n - name: MSI\n value: \"true\" \n```\n\nAs a result code in `EditorController.cs` will add `Authentication=ActiveDirectoryMsi` to connection string and `Uid` is `principalId` (or sometimes referred ad objectId) of the ManagedIdentity\n\n```csharp\n if ( \"true\".equals(System.Environment.getEnvironmentVariable(\"MSI\")) )\n connectionString = string.Format(\"Server=tcp:{0}.database.windows.net,1433;Database={1};Uid={2};Authentication=ActiveDirectoryMsi;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;\", conn.DBServer, conn.DBName, conn.Username, conn.Password);\n ```\n\n Read more in AAD settings for ODBC Driver in [Using Azure Active Directory with the ODBC Driver](https://docs.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver15)\n\n 9. Apply manifest and run in the AKS, verify by a providing server, db name and MSI principal id - connection should succeed ( run for example `Select 1`) \n ```\n kubectl apply -f manifests/k8-manifest.yaml\n ```\n![docs](./docs/cloudquery-addmsi.png)\n\n\n## Test tokens\nAt NMI pod\n```\nwget --header \"podname: cloudquery-web-6575cb496f-4ghsn\" --header \"podns: default\" -S -O log http://127.0.0.1:2579/host/token/?resource=https%3A%2F%2Fvault.azure.net%0A\n```\n\nAt app pod:\n\n```\n curl http://169.254.169.254/metadata/identity/oauth2/token?resource=https://vault.azure.net\n```\n\ncheck logs at NMI pod\n```\ntime=\"2020-03-18T04:25:17Z\" level=info msg=\"matched identityType:0 clientid:e8e4##### REDACTED #####a026 resource:https://database.windows.net/\" req.method=GET req.path=/metadata/identity/oauth2/token req.remote=10.240.0.26\ntime=\"2020-03-18T04:25:18Z\" level=info msg=\"Status (200) took 360939084 ns\" req.method=GET req.path=/metadata/identity/oauth2/token req.remote=10.240.0.26\ntime=\"2020-03-18T04:25:18Z\" level=info msg=\"matched identityType:0 clientid:e8e4##### REDACTED #####a026 resource:https://vault.azure.net\" req.method=GET req.path=/metadata/identity/oauth2/token req.remote=10.240.0.26\n```\n\n# Use Managed Identity in ODBC for AlwaysEncrypted\nFirst follow the steps in previous chapted to enable AAD Pod Identity and MSI Authentication in ODBC driver connection.\n\nTo use MSI identity as part of Always Encrypted connection to KeyVault, use beta driver\ninstalled in this repo `Dockerfile`\n\n1. In this repo we have Debian and RedHat distros of the driver and add to `Dockerfile`\n```\nCOPY msodbcsql_17.5.1.2-1_amd64.deb ./msodbcsql_17.5.1.2-1_amd64.deb\nRUN ACCEPT_EULA=Y apt-get -y --no-install-recommends install ./msodbcsql_17.5.1.2-1_amd64.deb\n```\n2. To use MSI when connecting to KeyVault and enable AlwaysEncrypted add to connection string `ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultManagedIdentity;KeyStorePrincipalId={2}` where `KeyStorePrincipalId` is assigned to MSI Identity principal Id. Example in `EditorController.cs` \n\n```\nif ( \"true\".equals(System.Environment.getEnvironmentVariable(\"ODBC_ALWAYSENCRYPTED\")))\n connectionString = string.Format(\"Server=tcp:{0}.database.windows.net,1433;Database={1};Uid={2};Authentication=ActiveDirectoryMsi;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultManagedIdentity;KeyStorePrincipalId={2}\", conn.DBServer, conn.DBName, conn.Username, conn.Password);\n``` \nand enable `ODBC_ALWAYSENCRYPTED` variable in K8S manifest, see example in `manifests/k8s-manifest.yaml`\n\nRead more in AAD settings for ODBC Driver in [Using Azure Active Directory with the ODBC Driver](https://docs.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver15)\n\n2. Create KyeVault and grant to MSI Identity access policy to use the Master Key in the vault\n\n```\naz keyvault set-policy --name --key-permissions get list sign unwrapKey verify wrapKey --resource-group php --spn \n```\n![docs](./docs/keyvault-policy.png)\n\n3. Apply Always Encrypted settings on the column in DB (use SSMS)\n\ncreate table with data and use SSMS as described here to create CMK/CEK and alter table\n[Always Encrypted: Protect sensitive data and store encryption keys in Azure Key Vault](https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault?tabs=azure-cli). Example script generated by the wizard could be found at `encrypt.ps1`\n\n```sql\nCREATE TABLE Persons (\n PersonID int,\n LastName varchar(255),\n FirstName varchar(255),\n Address varchar(255),\n City varchar(255)\n);\n\ninsert into Persons values (1, 'Smith','John', '222 bay', 'toronto');\ninsert into Persons values (1, 'Smith','Jake', '222 bay', 'toronto');\ninsert into Persons values (1, 'Lannister','Tyrion', '222 bay', 'toronto');\n```\nGo thru wizard\n![docs](./docs/ae-columns.png)\n\nas a result data will be enctypted in db and you'll see CMK provisioned in the vault.\n![docs](./docs/keyvault-cmk.png)\n![docs](./docs/sql-encdata.png)\n\n4. Deploy and test K8S app\n ```\n kubectl apply -f manifests/k8-manifest.yaml\n ```\n![docs](./docs/cloudquery-ae.png)\n\n\n# Sample T-SQL to provision CMK/CEK in code\nTo encrypt columns using T-SQL refer to article: [CREATE COLUMN MASTER KEY](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-column-master-key-transact-sql?view=sql-server-ver15) and [ALTER COLUMN ENCRYPTION KEY](https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-column-encryption-key-transact-sql?view=sql-server-ver15)\n\nProcess will envolve following steps:\n1. Create Master Key\n\n```sql\nCREATE COLUMN MASTER KEY MyCMK \nWITH ( \n KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT', \n KEY_PATH = N'https://.vault.azure.net:443/keys/ \n MyCMK/'); \n```\n2. Create Column Encryption Key\n\n```sql\nCREATE COLUMN ENCRYPTION KEY MyCEK \nWITH VALUES (\n COLUMN_MASTER_KEY = MyCMK,\n ALGORITHM = 'RSA_OAEP',\n ENCRYPTED_VALUE = '')\n```\n\nWhere encrypted value calculated in code example C#:\n\n```csharp\n SqlColumnEncryptionAzureKeyVaultProvider kvProvider =\n new SqlColumnEncryptionAzureKeyVaultProvider(\n KyvosKeyVaultService.GetKeyVaultClientAuthCallback(tenantId, clientId, clientSecret));\n\n byte[] cekRawValue = new byte[32];\n RandomNumberGenerator csprng = new RNGCryptoServiceProvider();\n csprng.GetBytes(cekRawValue);\n\n string mekPath=\"https://.vault.azure.net:443/keys/MyCMK/\";\n byte[] cekEncryptedValue = kvProvider.EncryptColumnEncryptionKey(mekPath, @\"RSA_OAEP\", cekRawValue);\n var encryptedKeySerialized = \"0x\" + BitConverter.ToString(cekEncryptedValue.Replace(\"-\", \"\");\n```\n[Using column master key store providers for programmatic key provisioning](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/develop-using-always-encrypted-with-net-framework-data-provider?view=sql-server-ver15#using-column-master-key-store-providers-for-programmatic-key-provisioning)\n\n## Operator Kube Deployment\n\n### Managing Server and DB\nDeploy SQL Server and DB (update firewall as needed)\n```\nkubectl apply -f manifests/azure_v1_sqlserver.yaml\nkubectl apply -f manifests/azure_v1_sqldatabase.yaml\nkubectl apply -f manifests/azure_v1_sqlfirewall.yaml\n```\n\nVerify Secrets were created and Server is in Ready state\n\n```\nkubectl get secret sqlserver-query\nkubectl describe azuresqlserver sqlserver-query\n```\n\nSecret will have SQL Server Admin user,fully qualified servername, username and password\n\n```\n$ kubectl get secret sqlserver-query -o yaml\napiVersion: v1\ndata:\n azuresqlservername: \n fullyqualifiedservername: \n fullyqualifiedusername: \n password: \n username: \nkind: Secret\nmetadata:\n creationTimestamp: \"2019-10-25T01:24:31Z\"\n name: sqlserver-query\n```\n### Managing DB and DBUsers only\n\nCreate Azure SQL Server outside the operator (ARM/Terraform/Powershell), now create Database using operator pointing to the name of the server (only logical name)\n\n```\nkubectl apply -f manifests/azure_v1_sqldatabase.yaml\n```\n\nExample Database yaml\n```\napiVersion: azure.microsoft.com/v1alpha1\nkind: AzureSqlDatabase\nmetadata:\n name: sqldatabase-sample465\nspec:\n location: eastus2\n resourcegroup: search\n # Basic=0; Business=1; BusinessCritical=2; DataWarehouse=3; Free=4;\n # GeneralPurpose=5; Hyperscale=6; Premium=7; PremiumRS=8; Standard=9;\n # Stretch=10; System=11; System2=12; Web=13\n edition: 5\n server: sqlserver-query # Name of the SERVER\n ``` \n\nCreate secret with SQL Server Admin credentials - it will be used to provision DB User (will be in AKV in the future)\n\n```\nkubectl \\\n create secret generic sqlserver-query-adminsecret \\\n --from-literal=azuresqlservername=\"sqlserver-query\" \\\n --from-literal=password=\"\" \\\n --from-literal=username=\"\" \\\n```\n\nCreate DB User using operator\n\n```\nkubectl apply -f manifests/azure_v1_sqldbuser.yaml\n```\n\nExample DB User:\n\n```\napiVersion: azure.microsoft.com/v1alpha1\nkind: AzureSQLUser\nmetadata:\n name: sqldb-readonlyuser\nspec:\n server: sqlserver-query\n dbname: sqldatabase-sample465\n adminsecret: sqlserver-query-adminsecret\n # possible roles:\n # db_owner, db_securityadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, db_denydatareader\n roles:\n - \"db_datareader\"\n```\n\nOnce DB and DB user finish provisioning, you will get a K8S secret object containing data to establish SQL connection:\n\n```\n$ k get secret sqldb-readonlyuser -o yaml\napiVersion: v1\ndata:\n password: \n sqlservername: \n sqlservernamespace: \n username: \nkind: Secret\n```\n\n### Create Failover Group for GeoReplication\n\nCreate Secondary Azure SQL Server, and then create FailoverGroup using K8S CRD pointing to primary and secondary servers\n\n```\napiVersion: azure.microsoft.com/v1alpha1\nkind: AzureSqlFailoverGroup\nmetadata:\n name: azuresqlfog-sample\nspec:\n location: eastus2\n resourcegroup: search\n server: sqlserver-query\n failoverpolicy: automatic\n failovergraceperiod: 30\n secondaryserver: sqlserver-query-secondary\n secondaryserverresourcegroup: search\n databaselist:\n - \"sqldatabase-sample465\"\n```\n\n\napplication is binding secrets to `\"/etc/sqlservice\"` path , to verify run cat command on the pod\n```\n$ k exec -it -n default cloudquery-web-xxxxxxx -- ls /etc/sqlservice\nazuresqlservername fullyqualifiedusername username\nfullyqualifiedservername password\n```","has_starred","hub_user","immutable_tags_settings",{"_75":11,"_76":77},"enabled","rules",[78],".*","is_automated","permissions",{"_82":21,"_83":11,"_84":11},"read","write","admin","user","storage_size",242129448,"orgData","recentTags",[91],{"_92":44,"_93":94,"_95":96,"_97":98,"_99":100,"_50":117,"_118":94,"_119":33,"_120":56,"_57":121,"_122":123,"_124":108,"_125":117,"_126":66,"_127":21},"content_type","creator",3217328,"full_size",115393522,"id",100379388,"images",[101],{"_102":103,"_104":105,"_106":48,"_107":108,"_109":110,"_111":112,"_113":48,"_114":-5,"_115":96,"_63":66,"_116":-5},"architecture","amd64","digest","sha256:b2c544b1eab653219752a0c8c4b3483063752286cb269eb254693b76dc948cce","features","last_pulled","2026-10-09T11:08:26.181011141Z","last_pushed","2020-05-18T03:34:11Z","os","linux","os_features","os_version","size","variant","2020-05-18T03:34:10.112039Z","last_updater","last_updater_username","media_type","latest","repository",9041619,"tag_last_pulled","tag_last_pushed","tag_status","v2","sbxKitMetadata","isPotentialSpam","isHubSbxKitsEnabled","showSbxLogoOverride","sbxTrademarkText","displaySecurityMetadata","tagAttestations","tagsHaveAttestations",[],"selectedTag","attestationsUrl","routes/_layout.r.$namespace.$reponame.tags",{"_38":141,"_148":149,"_135":158,"_159":11},{"_40":142,"_42":143,"_45":46,"_47":48,"_49":11,"_50":51,"_52":53,"_54":144,"_57":35,"_32":33,"_58":59,"_60":44,"_61":62,"_63":64,"_65":66,"_67":-5,"_68":62,"_69":70,"_71":11,"_72":33,"_73":145,"_79":11,"_80":147,"_85":33,"_86":87},[],[44],[56],{"_75":11,"_76":146},[78],{"_82":21,"_83":11,"_84":11},"tags",{"_150":64,"_151":-5,"_152":-5,"_153":154},"count","next","previous","results",[155],{"_92":44,"_93":94,"_95":96,"_97":98,"_99":156,"_50":117,"_118":94,"_119":33,"_120":56,"_57":121,"_122":123,"_124":108,"_125":117,"_126":66,"_127":21},[157],{"_102":103,"_104":105,"_106":48,"_107":108,"_109":110,"_111":112,"_113":48,"_114":-5,"_115":96,"_63":66,"_116":-5},[],"canDeleteTags","actionData","errors"]

kedubuis/new

Tags for kedubuis/new