.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL Agent - service account permissions - SQL Server 2008

Posted By:      Posted Date: August 30, 2010    Points: 0   Category :Sql Server
Hi @ all   I installed two SQL Server 2008 on Server 2008 R2 Std (principal and mirror) and an AD Server 2008, with sperate service accounts, connect as SA, localy all works fine. I created some agent tasks (PowerShell, T-SQL), but I get some Error massages in the history, that service account of SQL Agent didn't have the permission to query a remote machine(access denied for wmi (HRESULT: 0x80070005 (E_ACCESSDENIED)) and linked database(SQLSTATE 42000 Error(7314)). The simple query with SA permissions on the remote machine works and the powershell scripts with the local domain user works too. But not with the SQL Agent. WHY?? Where ist the different between the user account permisions and service account permissions? Which settings are needed? Example: get-wmiobject -class win32_service -computername 192.168.xxx.xxx| where {$_.name -like '*SQL*'} Powershell Console: works                                     SQL Agent Job: access denied I tried some solutions with user rights, group policies and security permissions but nothing works. like: Configuration -Service Accounts, SQL Server or SQL Server Agent service account http://support.microsoft.com/kb/283811/en-us http://msdn2.microsoft.com/

View Complete Post

More Related Resource Links

Cannot start SQL Server Agent service on Cluster , 2008 R2



I'm testing a two node cluster installation on windows 2008 r2 and sql server 2008 r2 both enterprise before a production deployment and my only issue so far is that the SQl Server Agent service cannot be started.

The failover works fine, the cluster and SQL services changes nodes without any problem but on both nodes i cannot start that service.

I'm using a domain user for the service, member of the local admin and sysadmin on the SQL server itself.

The error i get when i click on start from SQL management console is:

Unable to start service SQLAGENT$SQLTEST on server SERVERNAME. (mscorlib)

Program Location:

Server stack trace: 
 at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Service.Start()
 at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args,
 Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md,
 Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink
replySink) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.R

SQL 2005/2008 on Windows Server 2008 R2 cluster - Service only starts when account is local admin


I have a single node Windows Server 2008 R2 cluster configured with SQL Server 2008 Enterprise Edition installed as a clustered instance.

I am having an issue where the SQL Server service will only start using TCP/IP when the service account is a member of the local admins group. To get SQL to install I had to add the sql server service account to the local admins group.

I have configured group policy with the permissions required for SQL Server 2008 and this is correctly applied to the node.

Errorlog shows the following...

Error: 26054, Severity: 16, State: 1.
2010-05-05 15:23:41.98 Server      Could not find any IP address that this SQL Server instance depends upon.  Make sure that the cluster service is running, that the dependency relationship between SQL Server and Network Name resources is correct, and that the IP addresses on which this SQL Server instance depends are available.  Error code: 0x5.

2010-05-05 15:23:41.98 Server      Error: 17182, Severity: 16, State: 1.
2010-05-05 15:23:41.98 Server      TDSSNIClient initialization failed with error 0x5, status code 0xa. Reason: Unable to initialize the TCP/IP listener. Access is denied.

2010-05-05 15:23:41.98 Server      Error: 17182, Severity: 16

sql server agent roles and permissions

hi does any one have idea of what kind of roles are needed for creating a SQL server agent job and why those roles are needed?? Please Reply ASAP Because i don't have sysadmin rights.........so that other developers can use the same login for creating the jobs Thanks in advance

Enabling SA account on SQL Server 2008 R2

Hi I'm setting up a SharePoint 2010 development machine and hit a snag. I installed SQL Server 2008 R2 with the default instance (devserv1\mssqlserver). That's configured for mixed mode authent and I can login using either Windows Auth or SA no worries. Note that it's just a local machine account and NOT a domain account (this is an isolated VM with no domain) I installed SharePoint 2010 on the machine. I don't recall changing any settings and during the install, it has created a new instance called devserv1\sharepoint. I can login to it using Windows Authentication, but I don't appear to have any permissions. Also, the SA user is disabled, but I can't enable it, because I get a permissions error when using my windows account to do it... One thing I noticed that is a bit weird is the version numbers of the 2 instances - the first/default instance is 10.5.0.* whereas the one created by SharePoint install is or thereabouts - i.e., it's SQL 2008 but not R2 which I don't reallty understand. Anyway, how can I enable the sa account on the sharepoint instance? Thanks.weirdbeardmt, PhD, BSc. Silly name, but lots of letters.

SQL Server 2008 Agent Fails to start in a Win 2008 Cluster

When I try to bring SQLAGENT Online, I get the following errors: EventID:53 [sqagtres] StartResourceService: Failed to start SQLSERVERAGENT service.  CurrentState: 1 [sqagtres] OnlineThread: ResUtilsStartResourceService failed (status 435) [sqagtres] OnlineThread: Error 435 bringing resource online.   I just did a fresh SQL Cluster install as well.  When I installed the cluster, the service account was good to go.  I cant figure out what is going on.  SQL Server Engine starts fine.  Any ideas?

Get Service Account Details of the SQL Agent Service

Hello, How can I get the Service Account name for the SQL Agent service for a particular SQL Server (SQL 2005). Is that possible to get using SQL statements? Thanks, Prabhat

User Profile Service account Write to AD Permissions

I followed this guide here (http://www.harbar.net/articles/sp2010ups.aspx) to provision the UPS service in sharepoint 2010. I found the guide very helpful and informative. Everything is working correctly except for the write back to AD I've followed the steps and have assigned the listed permissions to the UPS service account, however I still get permissiong errors in the FIM GUI Our AD is running in a 2008 environment but is in 2003 mode, so I made sure to add the UPS account to Pre Windows 2000 Compatible access built in group and restart the server so that the new group settings would take affect. Do I need to reprovision the UPS service or something? Or am I missing something completely. (Hopefully the latter lol!) Thanks RKB

Issues in GetItemDataSources web service call in 2008 Report Server R2

Hi, I'm using Reporting Services web services endpoint ReportService2005.asmx with 2008 report server R2 and I'm facing some issues in getting the data source names for a report definition using the GetItemDataSources web service. The problem is that the data source names returned are different from the names of the actual data sources on the report server. The report has the following data sources: 0 1 _0 _A Data Source And the names returned by the GetItemDataSources for 2008 report server R2 is: AutoGen_0 AutoGen_1 AutoGen__0 AutoGen__A Data_Source When I make the same calls using a 2005 report server, the names returned are the same as that in the report definition. To summarize, only numeric data source names are changed to "Autogen_<number>"(eg. Autogen_0), data source names beginning with "_" are changed to "Autogen_<data source name>"(eg. AutoGen__A)  and spaces are replaced by an "_".   What is the problem for the data source names returned for 2008 report server R2? Or has there been any change in the way in which report server stores the data source names? Thanks, Kulbhushan Singhal.  

SQL Server 2008 R2 Installation and Service Pack 1 Question

I am migrating a SQL 2000 server to a SQL 2008 R2 server (on different physical servers) . When I ordered SQL Server 2008 the volume licensing allowed me to download sql server web 2008, sql server web 2008 R2 and sql server 2008 service pack 1. I don't know why I have three options to download. Don't I just need to just install the sql server 2008 Web R2? Are there any sql 2008 R2 service packs available?   

SQL Server 2008 express SQL Server Service won't start

Could really use some help. Am running XP Pro SP3.  Had 2005 Express running fine previously.  Installed 2008 Express but cannot get the SQL Server service to start.  SQL Server Agent does start though.  In log (below) it seems to be trying to access an E:\ drive that doesn't exist (did default install to C:\Program Files) - plus other issues.  Have 5gig+ free, so not sure why it's saying not enough space.  Have RTFMd the problem for a whole day with no luck.  Any advice much appreciated.   Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)  Apr  2 2010 15:53:02  Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) (Hypervisor)   Error: 17054, Severity: 16, State: 1. The current event was not reported to the Windows Events log. Operating system error = 1502(The event log file is full.). You may need to clear the Windows Events log if it is full. (c) Microsoft Corporation. All rights reserved. Server process ID is 4988. Authentication mode is WINDOWS-ONLY. Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG'. This instance of SQL Server last reported using a process ID of 3232 at 9/4/2010 9:36:12 AM (local) 9/4/2010 1:36:12 PM (UTC). This is an informational

Problem consuming 32bit WCF service which references a native Dll in a 64 bit OS(Windows Server 2008

I have some 32bit Dlls which i want to use it in 64bit OS as i do not have a source code of this to recompile it for 64 bit OS. I have created a x86(32 Bit) WCF service which calls these Dlls and hosted it as a Windows Service on a x64(64 Bit) machine using Windows Server 2008. I have created one more x64 client which calls a function in the WCF which in turn calls a function from the 32 bit native Dll. Whenever i try calling this WCF from the client I get an error as given below.Can anybody please guide as to where I am going wrong and what is needed to solve this issue. Thanks in advance for any help.******The Error Message********************System.BadImageFormatException: Could not load file or assembly 'TestWCFWrapper, Version=, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An attempt was made to load a program with an incorrect format.File name: 'TestWCFWrapper, Version=, Culture=neutral, PublicKeyToken=null'   at TestWrapperWCFClient.Form2.button1_Click(Object sender, EventArgs e)   at System.Windows.Forms.Control.OnClick(EventArgs e)   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)   at System.Windows.Forms.Control.WndProc(Message& m)   at System.Windows.Forms

Sql Server 2k8 Agent Service

I was going thru the tutorial and stopped sql server agent service with 'other' as the start mode, and now I cannot get it to come back online. I received the 0x800706be error. I also tried using a built in account and that didn't work either.

SQL Server 2008 R2 Setup & Service Packs

Hello I installed SQL Server 2008 R2. (Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor) ) Are there any Service Packs for SQL Server 2008 R2 available i need to apply? Thanks V

Service Account for SQL Server 2000 Cluster

Dear All, One of my customer is running a active/active SQL 2000 cluster in Windows 2003 and the service account for one of the instance is LocalSystem account. I am not sure how they changed the service account to localsystem because SQL Server doesn't allow LocalSystem account as your service account when you install it on a cluster and it doesn't allow to change the service account to LocalSystem account in Enterprise Manager when running in a cluster. I searched for documentation to findout whether Microsoft supports running SQL Server using LocalSystem account in a cluster but I am unable to find a documentation. Please help me to identify the documentation regarding the support or share your experience Regards BalajiRegards,Balaji, Please mark as answer if you think this answers your questions

Does SQL Server 2008 R2 Express Reporting Service support Shared Datasets?

I have spent a few hours trying to get this to work. I am using Report Builder 3.0 (locally), with SQL Server 2008 R2 Express Edition on a remote web server. I am running Report Builder as an Admin under Win7 64bit. All is OK until I attempt a Save of a newly created shared dataset. The error message is: Failed to save dataset "mydataset.rsd" The feature "Shared dataset" is not supported in this edition of Reporting Services. Details: The feature: "Shared dataset" is not supported in this edition of Reporting Services. ---> Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedException: The feature: "Shared dataset" is not supported in this edition of Reporting Services. I have researched the web, and the list of features for Express Edition does not list this feature as not being supported, although it does list "Shared component library" as being missing. Could anyone please confirm the situation please. If it is  a bug, is there any fix or workaround?  John Walton  

SQL SERVER 2005/2008 Proxy Account Set up

Hi All,I need to clear the logic behind Proxy account in SQL server 2005/2008.1.If i want to create a proxy account to run the xp_cmd shell system stored procidures ?2.And if i want to run the job steps what i understand here is:For:1 .above we can create a SQL/Windos Login and we can map this login to master database. it will create a user in master database, and we can aisign this user in master database xp_cmd shell extended strored procidure access from the securibles node or by running the scripts, however if this login/user dont have sysadmin access he can not run the xp_cmdshell extended stored prcidure in sql server query analyzer to achieve this task we need to create a credential using the EXEC sp_xp_cmdshell_proxy_account 'Windows LOGIN NAME','Password'.Here 'Windows LOGIN NAME' can be any windows account having least privilage(can not have syadmin privilage).For 2:To Run job steps using proxy account is where we have to use gui(off course ready scripts also)  but for that we need to create a cridential first if you are using gui:a) give name of cridentaial b) choose any windows account(haveing no sys admin access) and give the password for this windows account, make sure you are giving the correct password here.so now credentail part is done now go to proxy under sql server agentright click on proxy> new proxy> give a name to proxy, in credenti

Service pack for SQL Server 2008 R2

Hi all, I did SQL Server 2008 R2  Installation,I want update with Service packs,Please help what are latest services packs available.       Thanks in AdvanceSNIVAS
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend