Source, Ingest, Prepare, Analyze and Consume
Security is key factor in your operational consistency. You might have a Azure Key Vault configured like this;
Click on “Private endpoint and selected networks”. Click on “Add existing virtual networks” to allow communication between internal network.
This setting will enable internal services to access key vault. The selected internet facing IPv4 addresses will have access to key vault resource.
I am going to create and configure Azure key vault in this demo. I am assuming that an app services has already been provisioned. We need to make a configuration change for SQL connection so that Key Vault secret can be used without touching the application code.
Provision the Azure Key Vault
For this demo, I am going to use new permission model that is based on Azure role-based access control (RBAC).
I am leaving it as Public endpoint to match the App service configuration.
Click on Review + Create button to start the deployment.
Once created, add yourself to the role “Key Vault Administrator” via the Access Control (IAM). Now you can start managing secrets.
Add the connection string to the Key Vault secrets.
Secret type (Upload options) “Manual”, give it a name and set the value to the SQL Database connection string to your Azure SQL or Azure SQL VM.
Click on Create. Once created, click on the secret to see the secret details, then click again on the current version.
Copy Secret Identifier to your clipboard.
Add the Secret identifier reference to the Azure App Service Settings
Open the App Service configuration settings, and ad a new Connection string setting.
Type the name of the connection string (“SqlConnectionString” for application) and set the value. You can use the same name that you have used in appsettings.json file. Set the value;
@Microsoft.KeyVault(SecretUri=VALUE_FROM_CLIPBOARD)
Click on Save to save the app settings.
Allow the App Service to access the Key Vault
On the App Service, click on Identity to enable the System Assigned identity. Click on save after turning “On” the status.
Click on the “Role Assignments” button and then click on the “Add role assignment”. In the role assignment, choose scope “Key Vault”, subscription the subscription where you created the Key Vault on previous steps and the name of the Key Vault resource. For the role just select “Key Vault Secrets User (preview)”
You can go to the appsettings.json/web.config file of your application and clear the connection string value;
Visit your website and see if it loads successfully. The connection string is safely stored in the Azure Key Vault, and it’s no longer stored on the file system.
Known issues
ERROR: You might get an error “Keyword not supported: ‘@microsoft.keyvault(secreturi'”. I have experienced that the RBAC permissions can take a one or two minutes to be applied, so try after a few minutes. Also try restarting the application thought the App Service portal so nothing is cached.
another error might be this;
ERROR: Format of the initialization string does not conform to specification starting at index 0.
Check your connection string. it has spaces or is not right.
Resources
https://docs.microsoft.com/en-us/azure/key-vault/general/security-overview
https://docs.microsoft.com/en-us/azure/key-vault/secrets/quick-create-portal
https://docs.microsoft.com/en-us/azure/key-vault/general/developers-guide
If you want to run SSIS Packages from SQL Server Agent, the job might fail because of security context. Here are the steps that can help solve this problem;
Creating a credential to be used by proxy
USE MASTER
GO
--Drop the credential if it is already existing
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N'SSISProxyCredentials')
BEGIN
DROP CREDENTIAL [SSISProxyCredentials]
END
GO
CREATE CREDENTIAL [SSISProxyCredentials]
WITH IDENTITY = N'server\ServiceProxyAll',
SECRET = N'secret'
GOCreating a proxy account, Drop the credential if it is already existing
USE msdb
GO
--Drop the proxy if it is already existing
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N'SSISProxyDemo')
BEGIN
EXEC dbo.sp_delete_proxy
@proxy_name = N'SSISProxyDemo'
END
GOCreate a proxy and use the same credential as created above
--Create a proxy and use the same credential as created above
EXEC msdb.dbo.sp_add_proxy
@proxy_name = N'SSISProxyDemo',
@credential_name=N'SSISProxyCredentials',
@enabled=1
GOTo enable or disable you can use this command
EXEC msdb.dbo.sp_update_proxy
@proxy_name = N'SSISProxyDemo',
@enabled = 1 --@enabled = 0
GOGranting proxy account to SQL Server Agent Sub-systems
USE msdb
GO
--You can view all the sub systems of SQL Server Agent with this command
--You can notice for SSIS Subsystem id is 11
EXEC sp_enum_sqlagent_subsystems
GOGrant created proxy to SQL Agent subsystem, You can grant created proxy to as many as available subsystems
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
@proxy_name=N'SSISProxyDemo',
@subsystem_id=11 --subsystem 11 is for SSIS as you can see in the above image
GOView all the proxies granted to all the subsystems
EXEC dbo.sp_enum_proxy_for_subsystemGranting proxy access to security principals
USE msdb
GO
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are allowed to use any proxy
EXEC msdb.dbo.sp_grant_login_to_proxy
@proxy_name=N'SSISProxyDemo'
,@login_name=N'LABSVR01\ServiceProxyAll'
--,@fixed_server_role=N''
--,@msdb_role=N''
GOView logins provided access to proxies
EXEC dbo.sp_enum_login_for_proxy
GOFollow this article setting up proxy account using UI and setting up SSIS environment to use Azure Storage.
Reference
Check this web site;
use any stock symbol, aal, ual, aal, msft, amd, mu, spce, astr, mnts, arkx and see whether its available for sale or on hold. do a research on google and see last 5 years history. see Mkt capital and P/E (< 30). Example of overpriced stocks, Tesla.
Check Business and Science & Technology News (Google, Facebook). Dr. Shahid Massod is another resource for international stocks.
Apple, Microsoft stocks are safe. TX deals in precious metal and they are new but risky. Other options are 401K, IRA. Age over 50 can contribute more. Check fidelity also.
Check housing market. when fed interest rate increases, housing price fell. it’s time to invest. when fed interest decrease, refinance helps.
Create kids brokerage accounts in Charles Schwab or Fidelity. Buy big companies share. Big company is at least more than 50B. Look at last 5 years stock price. for example, if share price were $100 5 years ago and now it’s $500 then it’s 500% growth. Good company to invest.
Student Loan’s cost about 4-6%. grab load and invest in stocks that yield about 15%. Offset loan amount with invested amount and keep the gain.
Try to do job on 1099 or Corp-to-corp. Employer do not need to worry about social security and Medicare. The rate difference is around $8-10. This can help to save for old age.
if you are using this connection string in SSIS using windows authentication
Data Source=myserver;Initial Catalog=mydb; Integrated Security=SSPI;Connect Timeout=15;You will be able to develop your package and run. When it’s get deployed to SQL Server and someone try to run the package, they will get this error.
The reason, windows computer is not joined with Active Directory.
If you still want to run your SSIS package on server, use SQL Server authentication and use this connection string;
Data Source=myserver;Initial Catalog=mydb;User Id=myuser;password=mypassword;Connect Timeout=15;Hope this will help