Copy to the folder “%USERPROFILE%\Documents\My Shapes” (“C:\Users\you_user\Documents\My Shapes”; that is the default folder for the Visio custom shapes)
To access the shapes in Visio, select the Shapes Windows:
“More Shapes -> My Shapes -> Microsoft Integration Stencils v#.#”
“More Shapes -> My Shapes -> MIS Apps and Systems Logo Stencils v#.#”
“More Shapes -> My Shapes -> MIS IoT Devices Stencils v#.#”
“More Shapes -> My Shapes -> MIS Support Stencils v#.#”
Navigate to c:\windows\temp. On the temp folder, right click properties, security and advanced. Add your proxy account here and give it full permissions;
I am not getting into the discussion of pros and cons of xp_cmdshell. This command helps you to run broad level features inside your SQL server. You can run OS level commands, SSIS packages etc.
Here is how you can enable, disable and use.
To check, if xp_cmdshell is enabled and available to use;
SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM sys.configurations
WHERE name = 'xp_cmdshell';
Here is how you can enable it, use it and disable;
DECLARE @originalSetting INTEGER = 0;
SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'xp_cmdshell' ;
IF @originalSetting = 0
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
END
EXEC xp_cmdshell 'dir c:\';
IF @originalSetting = 0
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
END
You can create a stored procedure and use;
CREATE PROCEDURE sp_SuperCmdShell
(
@commandToRun VARCHAR(1000)
)
AS
BEGIN
-- check to see if xp_cmdshell is enabled, if its not
-- then enable it and run the command then set it back
-- to not enabled.
DECLARE @originalSetting INTEGER = 0;
SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'xp_cmdshell' ;
IF @originalSetting = 0
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
END
EXEC xp_cmdshell @commandToRun;
IF @originalSetting = 0
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
END
END
GO
exec #sp_SuperCmdShell 'dir c:\';
This is not a new feature. It came out with early release of SQL server. If you see the usage command, they are OS level command. With the super privileged access any body can run a “format c:\” OS command and leave your database server in unrecoverable state. My suggestion would be to use it with least privileged proxy account.
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'
GO
Creating 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
GO
Create 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
GO
Granting 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
GO
Grant 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
GO
View all the proxies granted to all the subsystems
EXEC dbo.sp_enum_proxy_for_subsystem
Granting 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''
GO
View logins provided access to proxies
EXEC dbo.sp_enum_login_for_proxy
GO
Follow this article setting up proxy account using UI and setting up SSIS environment to use Azure Storage.
