Month: May 2015

Enable Hardware Virtualization in Firmware

I ran into this when trying to enable Hyper-V on my machine.  I found that the option for Hyper-V Platform was greyed out so I couldn’t select it.  When I hovered over the message was "Hyper-V Cannot be installed: Virtualization is disabled in the firmware".  In order to make it selectable I had to enable virtualization in my BIOS firmware settings. 

Each computer is different but the steps should be similar.  The virtualization feature is usually called Virtualization Technology, Virtual Machine Extensions, VMX, VT, or something similar.  You can look for it in Advanced, CPU or Security features.  It will depend on your motherboard.

Here is how I enabled the setting on my machine.  To get to the BIOS, I closed all my programs and restarted the computer by going to:

PC Settings -> Update and Recovery -> Recovery -> Restart Now

image_thumb1

When the computer started up again I looked for advanced options.

image_thumb[3]

image_thumb[6]

In the Advanced Options I found the Firmware Settings

image_thumb[7]

I restarted the computer from here in order to bring up and make changes to the Firmware Settings.

image_thumb[9]

On restart, the setup utility came up.  I switched to the Configuration tab, and enabled Intel Virtual Technology, then saved the changes. 

As I mentioned above, the virtualization feature will be different on every machine and is usually called Virtualization Technology, Virtual Machine Extensions, VMX, VT, or something similar.  You can look for it in Advanced, CPU or Security features.  It will depend on your motherboard.

image_thumb[11]

You can now go back and enable Hyper-V on your machine, as it will no longer be greyed out.

Creating a Proxy User to run an SSIS package in SQL Server Agent

There have been a number of times over the years when I have had to create a Proxy user in SQL Server in order to provide needed access to connections and locations being used in an SSIS package.  Sometimes the SQL Server Agent login account simply doesn’t and shouldn’t have the required permissions. 

Before using a Proxy account, do check to see if the permissions issue isn’t just that the SQL Server Agent login account has been set up as ‘Local System’.  If that is the case, see if you can change it to a domain account specifically created for SQL Server Agent purposes.  Check Administrative Tools -> Services on the server where the SSIS SQL Server resides to see what login account the SQL Server Agent is mapped to.

The user mapped to the SQL Server Agent Service Account will need read/write permissions.  If you do need to create a new domain login for the SQL Server agent, in SSMS go to Server-> Security (not database security) -> Logins -> left click New Login -> Search -> Locations button -> Entire Directory -> select main domain ->OK -> Sql Agent username-> Check Names button-> OK-> Server Roles-> sysadmin-> OK..

If it turns out that you need to create an SSIS proxy user, edit this script to use the correct username and password and run it to create the proxy user.

USE master 
GO
-- Create a proxy credential for xp_cmdshell.
EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\username', 'password';--SELECT  * FROM [master].[sys].[credentials]
-- Grant execute permission on xp_cmdshell to the SQL Server login account. 
GRANT exec ON sys.xp_cmdshell TO [DOMAIN\username] 
GO

-- Create a credential containing the domain account PowerDomain\PowerUser and its password
CREATE CREDENTIAL MyCredential WITH IDENTITY = N'DOMAIN\username', SECRET = N'password'
GO
USE [msdb]
GO
-- Create a new proxy called SSISProxy and assign the PowerUser credentail to it
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MyProxy',@credential_name=N'MyCredential',@enabled=1
-- Grant SSISProxy access to the "SSIS package execution" subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MyProxy', @subsystem_id=11
-- Grant the login testUser the permissions to use SSISProxy
EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'DOMAIN\username', @proxy_name=N'MyProxy'
GO

You will be able to see the proxy user in SSMS under SQL Server Agent.  Is is in the SSIS PAckage Execution section because we added it to the SSIS subsystem in our code.

 

image

Now when you create your SQL Server Agent Job you can choose to run the SSIS package as your proxy user with the required permissions,rather than the SQL Server Agent account.

image