:::: MENU ::::

Blog

  • Aug 28 / 2013
  • 0
dbDigger, SET Options, SQL Server Error messages

Error message when creating assembly in a SQL Server database

I was required to transfer SQL Server assembly along with the other objects from one SQL Server instance to another. I used Transfer SQL Server Objects which is a handy control of SSIS. During the test process i get following error as a result of trying to create assembly in target database.

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: “ERROR : errorCode=-1073548784 description=Executing the query “CREATE ASSEMBLY [%]
AUTHORIZATION [dbo]
FRO…” failed with the following error: “CREATE ASSEMBLY for assembly ‘%’ failed because assembly ‘%’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.  The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}”.

This error is produced because before creating the assembly we have to mark the database as trustworthy. By default trustworthy mode is disabled for SQL Server databases. Trustworthy mode tells the server that this database contains controlled creation of objects and there is no chance that some one will create malicious objects in it or some one will manipulate it by attach/detach process. So if you are satisfied with above assumptions that you are going to give to your server then use following command in subject database and set it as a trustworthy database

ALTER DATABASE [DBNameHere] SET TRUSTWORTHY ON
GO

After this step we are now able to create and refer assemblies in SQL Server database and error exists no more.

  • Aug 27 / 2013
  • 0
dbDigger, PowerShell, SQL Server Agent scheduled Jobs, SQL Server Error messages

Error message while using PowerShell in scheduled job

I was required to call PowerShell script from SQL Server scheduled job. Following was the command to access and run the PS script saved on disk.
powershell -command “& ‘D:PscriptsTransfer.ps1’ ”

This command syntax may be saved in batch file or directly run from cmd. However surprisingly i was facing following error message in job history. It is notable that job status was successful however error messages were logged there in job history.

A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘powershell -command “&’%'”‘. Correct the script and reschedule the job. The error information returned by PowerShell is:

Apparently it was pointing towards syntax used in the command however i did not find any issue in the command. It was working fine outside the SQL Server scheduled job environment. Also i did not get any help through googling. However i analyzed the issue in terms of permissions that we mostly face in scheduled job environment.

So first step was to create a proxy account. I will cover the topic of creating proxy account for SQL Server agent job in a separate post. After creating the proxy account i used it in scheduled job and ran the job to test.
The idea worked and job executed successfully.
  • Aug 26 / 2013
  • 1
dbDigger, Security and Permissions, Server Level Configurations, System Administration, Xp_CmdShell

Enable and work with XP_CmdShell in SQL Server 2008 R2

Xp_CmdShell enables us to run cmd commands within T-SQL environment. The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. It requires SysAdmin rights to use Xp_CmdShell. When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using proxy account. As a security measure by default Xp_CmdShell is disabled and we have to enable it explicitly before use. If disabled then following error message will be used when tried to use

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

Enable Xp_CmdShell

We may enable Xp_CmdShell through SSMS GUI or T-SQL. So let us explore both the ways to enable Xp_CmdShell.
To enable Xp_CmdShell through SSMS GUI perform following steps.

  • Right click on server instance
  • Click on Facets
  • Choose Surface Area Configuration from facets drop down list
  •  Find Xp_cmdShell from the properties and set enabled to true
  • Click OK and Xp_CmdShell is enabled now

Following snaps will help you to perform above mentioned steps for enabling Xp_CmdShell through SSMS GUI.

To enable Xp_CmdShell through SSMS GUI step 1

 

To enable Xp_CmdShell through SSMS GUI step 2

To enable Xp_CmdShell through SSMS GUI step 3
To enable the Xp_CmdShell through T-SQL

 -- To allow advanced options to be changed.  
 EXEC sp_configure 'show advanced options', 1  
 GO  

 -- To update the currently configured value for advanced options.  
 RECONFIGURE  
 GO  

 -- To enable the feature.  
 EXEC sp_configure 'xp_cmdshell', 1  
 GO  

 -- To update the currently configured value for this feature.  
 RECONFIGURE  
 GO   

verify the current option

To verify the current status of xp_cmdshell you may use following T-SQL

-- Verify the current status of xp_cmdshell  
 SELECT * FROM sys.configurations where name = 'xp_cmdshell'  
 GO  

Using the Xp_CmdShell

Here i will quote a simple example of xp_cmdshell usage from BOL.
Executing the following xp_cmdshell statement returns a directory listing of the current directory.

 EXEC xp_cmdshell 'dir *.exe';  
 GO  
  • Aug 23 / 2013
  • 0
dbDigger, PowerShell, SQL Server Error messages, System Administration

File %.ps1 cannot be loaded because the execution of scripts is disabled on this system.

Now a days i am working on a powershell task. This morning i prepared a basic powershell script and run it to see that how close are the results. I called the powershell script from cmd so that any errors may be seen if generated. To my surprise it generated a different type of error pointing to some configuration

Error while executing power shell script

In a more readable text form the errror statement is

File %.ps1 cannot be loaded because the execution of scripts  is disabled on this system. Please see “get-help about_signing” for more details.

I looked for the solution and implemented it to allow PS scripts run on my stystem. Following is the brief solution that i implemented.

Step1: Check current system setting for scripts

launch powershell and execute Get-ExecutionPolicy 

http://2.bp.blogspot.com/-uucE5IKdRiQ/UhdOv5PTtpI/AAAAAAAADSU/mVI7kpc2HHg/s1600/Get+Policy+settings+for+scripts+execution

It shows that current script execution is restricted.

Step2: Change the script execution policy

Now we have to change the script execution policy. We have three other options to set.

  • RemoteSigned: You can run your own scripts but downloaded scripts will have to be signed in order to run on the system.
  • AllSigned: Your own or dowloaded scripts all should be signed to run.
  • Unrestricted: Remote or your own scripts may be run without signing check.

So you may choose an appropriate option and run one of the following command according to choice

  • Set-ExecutionPolicy RemoteSigned
  • Set-ExecutionPolicy AllSigned
  • Set-ExecutionPolicy UnRestricted

Step 3: Verify

You may verify the new policy implementation by repeating step 1. This time you will get the new policy name instead of restricted policy.

After implementing this solution i proceeded with my original tasks by writing and executing the PS scripts successfully. Hope same for you.

  • Aug 21 / 2013
  • 0
dbDigger, DDL, DDL Triggers, SQL Server Error messages, SQL Server Integration Services (SSIS), Triggers

Error generated in SSIS task

While importing few tables from server instance to another i got following error message.

http://4.bp.blogspot.com/-SQO5uAIPeh8/UhTljUKxEWI/AAAAAAAADR4/Xm_GZfJcKfQ/s1600/Problem+with+the+query+Result+Set+property+not+set+correctly,+parameters+not+set+correctly+or+connection+not+established+correctly

The user does not have permission to perform this action. Possible failure reasons: Problem with the query. “Result Set” property not set correctly, parameters not set correctly or connection not established correctly.

Error message was not looking fit for my scenario. In the mean while i got another report about errors being generated while creating tables on same server. The error message was pointing to failure of a DDL trigger meant for logging purpose. The trigger was being invoked on table creation and got failed due to permission issues while inserting logs in trigger log table. So i disabled the trigger and executed the task again. This time task was completed successfully.

So if you fall in above mentioned scenario then along with checking the reasons provided in error message also look for DDL trigger context.

  • Aug 16 / 2013
  • 0
dbDigger, Monitoring and Analysis, Replication, T-SQL Scripts

Get list of replicated objects in a database

If replication is implemented then it is important to have a list of objects that have been replicated. Replicated objects are known as article in terms of replication setup. So articles may have slightly different concerns in terms of performance, drop or truncate command. Use the following simple script to get the list of replicated objects in a database.

 USE [Ur DB Name Here]  
 GO  
 SELECT NAME AS [Object Name]  
   ,type_desc AS [Object Type]  
   ,is_published AS [Is Data and Schema Published]  
   ,is_schema_published AS [Is Only Schema Published]  
 FROM sys.objects  
 WHERE is_ms_shipped = 0  
 AND (is_published = 1 OR is_schema_published = 1)  

Get list of replicated objects in a database
[Is Data is Schema Published] is for tables. While [Is Only Schema Published] is for other objects like USPs, UDF and Views.

Consult us to explore the Databases. Contact us