Tag Archives: sql

SQL Job using LINK Server using Service Account

Leave a reply

There are usual errors when using a LINK Server while also using a Service account on SQL Job. Usual errors are as follows:

  1. Access to the Remote Server is Denied Because the Current Security Context is not Trusted
  2. EXECUTE AS USER failed for the requested [user] dbo in the [database]
  3. After resolving those 2 issue its still failed

Assumptions of your setup:

  1. You have created a credential object under Security > Credentials
  2. You have created a proxy that is using that credential under SQL Server Agent > Proxies > Operating System (CmdExec)

Here are the steps you need to do:

  1. Change database TRUSTWORTY to ON

    ALTER DATABASE SQLDB0 SET TRUSTWORTHY ON
    GO
  2. Reapply database owner. This is needed because the SID recorded is now mismatch. c/o Rob Reid on this post.

    ALTER AUTHORIZATION ON Database::[database] TO [domain\user]
  3. Update your SQL Job step to use CmdExec instead of T-SQL

    Job Step Properties
    – Type: Operating system (CmdExec)
    – Run as: Your service account credentials using a proxy
    – Command
    SQLCMD -I -b -S $(ESCAPE_DQUOTE(SRVR)) -d DatabaseName -Q"EXEC usp_YourStoredProcedure"

MS SQL Generate Script with Data

2 Replies

Here is a step on how you can make MS SQL Generate Script with Data.

  1. Open your Sql Server Management Studio.
  2. Select your database and right click on it follow just like what is in the image below. Refer to image 1
  3. Click Next until you when thru to the image below and click Advance. Refer to image 2
  4. Scroll down and until this row and update the value just like what is below. Refer to image 3
  5. Click Ok and next until you have generated the said script. Refer to image 4
  6. Now you have a create script that includes the schema at it’s data.

SQL Server Report Service

5 Replies

Time to remember an old friend on creating a report on ASP .Net, the Sql Server Report Service also known as SSRS. SSRS utilizes the MS SQL Server that simplifies the creation of report. The Concept is that you separate the location where your report is located so that even if your website is down you can still provide report and use it on other web programming language by putting it in an iFrame.

Upon reviewing here are the sites that made me remember:

In addition, SSRS need SQL Business Intelligence Development Studio (BIDS) in order to create reports and deploy directly these reports to the server.