We have a sqlserver 7 on windows nt 4.
We'd just change a startup account for sqlserver agent to a
not domain admin for security reason. The startup account for sqlserver service is
still domain admin. We are now getting the following error every 5 second
in our application event log.
"SQLServeragent security context does not have server-autorestart privileges"
Is there a way to get rid of this error without putting a domain admin as
a startup account for sqlserver agent?
SQL Server in on a ‘member’ server in my company domain (We took the ‘stand-alone’ option when installing NT on this server).
I have set up an NT domain account for SQL ServerAgent ‘Service startup account’ which is a different account than the NT domain account listed in the SQL Server Properties, Security tab, ‘Startup service account’.
I log on to this server with the login in the latter.
Replication is working OK, but my application log keeps filling up with the message “SQLServerAgent security context does not have server autorestart privileges”.
I'm currently creating a database that will only allow data operations through stored procedures, ie users will not be able to directly modify tables. I'd like to use an Insert trigger which will run in response to a stored procedure that inserts records. The trigger will check business logic and additionally modify records in a couple of other tables. Given that I've disallowed direct access to the tables, will it run or will the security set-up prohibit that?Thanks in advance for any answers.
I'm building a SQL function from C++ and compile my assembly with /clrafe. After that I create the assembly with SAFE permission sets and create the function, both in SQL. But the server don't accept load or execute the function because of permission error. It says:
Code Snippet
"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65561. The server may be running out of resources, or the assembly may not be trusted with PERMISSIONSET = EXTERNALACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues."
Does the terminology coince with the technology, or is it diffrent "safety" conditions we are speaking about? One for the SQL permission set, and another safety for CLR type-safe assemblies?
Anyway, after playing around a bit, and following the tricks from this thread. I managed to load and execute the function, but only in UNSAFE permission (unrestricted mode?)
---8<----
My second question is can I use unrestricted mode for assembly compiled with /clr (mixed CLR)?
I'm asking, because I have some C source code that I want to reuse, and for this I can't be type-safe and therefore need to compile only with /clr (mixed CLR).
Should I go back to the plain C API in SQL 2000 to implement such function in SQL 2005, and ignore all new things in c++/CLR/.NET?
If you have any points to C++ sample code, then please forward this to me.
some time back i had problem with sending the service broker messages on remote machine,
I had some security issues and they were resolved by the help of Remusu.
since the ip of the remote machine was changed in between so i just re-executed the same scrips which used to successfully send messages on the remote machine.(I just updated the IP in the route )
To my surprise same script did not work now where as no change have been made.
I am doing the following:
1.Created the certificate and end point on the sender side. back up the certificate in a file and copied to the other machine. same step was repeated for receiving side as well.
2.both the side i created the certificates using the back up files from other sidend proper authorization
3.Then I created the database,route,messagetypes,contract,queues,services etc both the side.
4. then i setup dialog security(ie.created the dialog security certificates both the sides and back up them). Later I copied these back up files to each other, create some dialog user and create certificate using authorization to these remote dialog users created.
5.I also created remote service binding on both the sides and granted send permission to the remote dialog user.
When i send the message from sender to the receiver, and run the profiler, I see that on the sending side none of the broker event gives any error.
In the recever side I get the followng Event: Broker:Message Undeliverable
This message could not be delivered because the security context could not be retrieved. Error 11229.
I m surprised that the same script was run in the same order,Why was it running before and not now.
I also checked the End points using telnet and they seem to be fine. Also the firewall was "Off" on both the machines i.e. there was no change in system state also.
The objective is to programmatically create an IIS virtual directory from within a stored proc. I already have a proxy account configured using the sp_xp_cmdshell_proxy_account proc. The SQL server is a member server to a domain and the proxy account is a member of Domain Admins (per iisvdir.vbs requirement). When I login to the server as the proxy account and run the dos command in a window it executes just fine. However, the above SQL statements result in the following in server management studio:
"You cannot run this command because you are not an administrator on the server you are trying to configure."
If the xp_cmdshell runs under the proxy account, and the proxy account has been verified to be able to login and run the script independently, what gives?
Edit: Just to make sure the proxy account was setup properly, I used the following cmd
select @cmd='set'
and it's results set shows
USERNAME=sqladmin
which is indeed the name of the proxy/windows account that I can run the dos script under when logged into windows...
I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found
On the Target Server:
on Broker: Message undeliverable --- This message could not be delivered because it is a duplicate
On the Source Server
on Broker: Message undeliverable --- This message could not be delivered because the security context could not be retrieved,
I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this
I need to allow only a specific group of users to use a linked server. Rather than granting each user access in the linked server I create and give access to a local SQL login "link_user" and then grant the group impersonate to "link_user". The folllowing statements then work fine:
EXECUTE AS LOGIN = 'link_user'
SELECT * FROM OPENQUERY(linked_server, 'SELECT * FROM ...)
But when I use the exact same statements in a stored procedure I get the folllowing error:
Msg 7416, Level 16, State 1, Procedure linktest2, Line 5
Access to the remote server is denied because no login-mapping exists.
I need to be able to work with the result set which is why I use OPENQUERY rather than EXECUTE... AS... AT which works fine also in stored procedures!?
Is there another workaround then to create all users in the linked server security?
I have 2 databases one for datawarehouse and one for reporting.
I created a view in the reporting database joining several tables from datawahourse. The view is meant for a users who uses powerpivot. It will contain a few million of rows.
I set up a role in the reporting database. I assigned select rights to that role on the view.
When the user wants to access the view, he can see the view but a select gives
The server principal "pp_user" is not able to access the database "datawarehouse" under the current security context.
How can I fix this ? I don't want to give the user select rights on tables in the datawarehouse.
I am experiencing a head-scratcher of a problem when trying to use a Linked Server connection to query a remote SQL Server database from our SAP R/3 system. We have had this working just fine for some time, but after migrating to new hardware and upgrading OS, DBMS, and R/3, now we are running into problems.
The target database is a named instance on SQL Server 2000 SP3, Windows 2000 Server. The original source R/3 system was also on SQL Server 2000 (SP4), Windows 2000 Server. I had been using a Linked Server defined via SQL Enterprise Manager (actually defined when the source was on SQL Server 7), which called an alias defined with the Client Network Utility that pointed to the remote named instance. This alias and Linked Server worked great for several years.
Now we have migrated our R/3 system onto new hardware, running Windows Server 2003 SP1 and SQL Server 2005 SP1. I redefined the Linked Server on the new SQL 2005 installation, this time avoiding the alias and referencing the remote named instance directly, and it tests out just fine using queries from SQL Management Studio. It also tests fine with OSQL called from the R/3 server console, both when logged on as the application service account with a trusted connection, and with a SQL login as the schema owner. From outside of the application, I cannot make it fail. It works perfectly.
That all changes when I try to use the Linked Server within an SAP custom program (ABAP), however. The program crashes with a database interface error. The database error code is 15274, and the error text is "Access to the remote server is denied because the current security context is not trusted."
I have set the "trustworthy" property on the R/3 database, I have ensured the service account is a member of the sysadmin SQL role, I've even made it a member of the local Administrators group on both source and target servers, and I've done the same with the SQL Server service account (it uses a domain account). I have configured the Distributed Transaction Coordinator on the source (Win2003) system per Microsoft KB 839279 (this fixed problems with remote queries coming the other way from the SQL2000 system), and I've upgraded the system stored procedures on the target (SQL2000) system according to MS KB 906954. I also tried making the schema user a member of the sysadmin role, but that was disastrous, resulting in an instant R/3 crash (don't try this in production!), so I set it back the way it was (default).
What's really strange is no matter how I try this from outside the R/3 system, it works perfectly, but from within R/3 it does not. A search of SAP Notes, SDN forums, SAPFANS, Microsoft's KnowledgeBase, and MSDN Forums has not yielded quite the same problem (although that did lead me to learning about the "trustworthy" database property).
Any insight someone could offer on this thorny problem would be most appreciated.
I have a login that is mapped to a Windows sysadmin account. I used it to login to Sql Server 2005. I then created a database called Freedom. I then added a Windows login and user called FreedomAdmin, with Freedom set as the default database. When I login in to Windows using FreedomAdmin and then try to login in to Sql Server 2005, I get the following error:
The server principal "FREEDOM1FreedomAdmin" is unable to access the database "master" under the current security context.
If the default database for FreedomAdmin is Freedom (and it is - I checked from my sysadmin login account), why can't I login. Must I give FreedomAdmin permissions to master?
"Could not impersonate the client during assembly file operation."
The CLR function is invoked from Service Broker internal activation stored procedure.
"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.
SqlContext.WindowsIdentity.Name is "NT AUTHORITYSYSTEM" as the Data Engine runs with the LocalSystem account.
How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?
Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".
From my desktop in Management Studio (MS) I can connect to server A and run a query like, select top 1 * from serverB.mydatabase.dbo.mytable, and all seems to be working. If I leave and come back after a few minutes it no longer works and I get this error: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
If I remote connect to server B and run the query it works and when I try it from my desktop MS it starts working again. Any ideas on why this is happening?
SQL2005 on winserver 2003. I have a view in Xdb that accesses tables in 2 different databases (Xdb and Ydb) on the same server. I have mixed mode security. I have a SQL user (XYuser) that has read access to all tables and views on both databases, yet when I try to access the view using a C# windows application I get the following error:
The server principal "XYuser" is not able to access the database "Ydb" under the current security context
This same scenario works under SQL 2000. I looked through the postings and tried to set TRUSTWORTHY ON on both databases but that didn't help. I can access any other views or tables on the SQL 2005 server, just not the one that joins the tables cross databases. Any help is much appreciated... john
I am unable to run a stored procedure that accesses a linked Oracle database as a dataset for a deployed report in Reporting Services. I receive the following error:
An error has occurred during report processing.
Query execution failed for data set 'spName'.
Access to the remote server is denied because the current security context is not trusted.
A few facts ...
The report previews fine in Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) report designer with VS being installed on my local machine and a shared data source referencing Server1 The deployed report on Server2 produces the error The stored procedure code is similar to the following:
SELECT x.FieldName as OracleField
,y.FieldName as SSfield
FROM OracleLinkedServer..DatabaseName.TableName x
LEFT JOIN Server1.dbo.TableName y
The deployed report runs fine when the stored procedure code is changed as follows. This indicates to me that the problem is truly an Oracle connection issue;
SELECT 'ABC' as OracleField
,y.FieldName as SSfield
FROM Server1.dbo.TableName y
The Oracle database is a linked database on both SQL Server1 and on SQL Server2 database engines. (I don't see where in Server2 Reporting Services I can specify additional server connections.) I've tried to trick Mr. Reporting Services on Server2 by creating a @L_TempTable in my stored procedure, inserting the data into it, then selecting from a SQL Server1.dbo.TableName LEFT JOIN @l_TempTable, but Server2 still knows I'm trying to get out to Oracle and rewards me with the error. I can effectively run the Server1 stored procedure script on Server2 through MSSMS -- it's just the deployed report that references the stored procedure that doesn't work The data source properties of the deployed report are as follows:
Select -- A Custom Datasource
Connection Type: Microsoft SQL Server
Connectoin String: Data Source=Server1;Initial Catalog=DatabaseName
Select -- Credentials stored securly in the report server
Username: UserName
Password: Password
Check -- Use as Windows credentials when connecting the the data source
Check -- Impersonate the authenticated user ...
(Please do not get sidetracked on the 'A Customer Datasource' selection above. I'd rather be using a shared data source, but right now, this is just the way it is.)
Anyone's expertise would be greatly appreciated. We have a great data warehouse but sometimes you just have to go back to the Oracle ERP and slug it out. What do I need to do to get a deployed report to effectively read from a stored procedure that references an Oracle table?
In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:
The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password. The remote database is set to Trustworthy. I have tried every combination of WITH Execute As on the remote stored procedure but nothing works. I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.
I am unable to run a stored procedure that accesses a linked Oracle database as a dataset for a deployed report in Reporting Services. I receive the following error:
An error has occurred during report processing.
Query execution failed for data set 'spName'.
Access to the remote server is denied because the current security context is not trusted.
A few facts ...
The report previews fine in Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) report designer with VS being installed on my local machine and a shared data source referencing Server1 The deployed report on Server2 produces the error The stored procedure code is similar to the following:
SELECT x.FieldName as OracleField
,y.FieldName as SSfield
FROM OracleLinkedServer..DatabaseName.TableName x
LEFT JOIN Server1.dbo.TableName y
The deployed report runs fine when the stored procedure code is changed as follows. This indicates to me that the problem is truly an Oracle connection issue;
SELECT 'ABC' as OracleField
,y.FieldName as SSfield
FROM Server1.dbo.TableName y
The Oracle database is a linked database on both SQL Server1 and on SQL Server2 database engines. (I don't see where in Server2 Reporting Services I can specify additional server connections.) I've tried to trick Mr. Reporting Services on Server2 by creating a @L_TempTable in my stored procedure, inserting the data into it, then selecting from a SQL Server1.dbo.TableName LEFT JOIN @l_TempTable, but Server2 still knows I'm trying to get out to Oracle and rewards me with the error. I can effectively run the Server1 stored procedure script on Server2 through MSSMS -- it's just the deployed report that references the stored procedure that doesn't work The data source properties of the deployed report are as follows:
Select -- A Custom Datasource
Connection Type: Microsoft SQL Server
Connectoin String: Data Source=Server1;Initial Catalog=DatabaseName
Select -- Credentials stored securly in the report server
Username: UserName
Password: Password
Check -- Use as Windows credentials when connecting the the data source
Check -- Impersonate the authenticated user ...
(Please do not get sidetracked on the 'A Customer Datasource' selection above. I'd rather be using a shared data source, but right now, this is just the way it is.)
Anyone's expertise would be greatly appreciated. We have a great data warehouse but sometimes you just have to go back to the Oracle ERP and slug it out. What do I need to do to get a deployed report to effectively read from a stored procedure that references an Oracle table?
We've got 3 SQL Servers all SQL Server 2012. We have a Master server and 2 production servers (let's call them A and B). The Master has linked servers defined for both A and B in an identical way.
A and B both have the same databases and each has a Stored Proc that gets called by the Master server. An SQL Agent job executes the stored proc on both A and B. It works fine against A but not B.
If we execute the stored proc in a query window under our windows account (domainowner) it works, if we use SetUser to pretend to be the SQL Server Agent account (let's call it domainagent) A works and B doesn't.
Executing against B returns the error "Access to the remote server is denied because the current security context is not trusted."
domainagent is a sysadmin on both Server A and B.
Trustworthy is set to ON in both the Master Server Database it's executing from and the remote databases on both A and B.
All 3 databases have the same owner (let's call it domainowner).
I am sorry for posting this question here. The only reason I am doing so is because Remus answered a question similar to this late last year, but I couldn't get access to the whole thread.
In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:
a.. The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password. b.. The remote database is set to Trustworthy. c.. I have tried every combination of WITH Execute As on the remote stored procedure but nothing works. d.. I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.
I have a stored procedure servicing a queue (sql 2005). The stored procedure updates some data in a different server and database. If the queue contains messages and I execute manually the stored procedure, I don't have any problem at all. However, if I set the queue to use the same stored procedure, it gives me the following error:
Error 15274: Access to the remote server is denied because the current security context is not trusted.
What would cause this message to display or what does this mean?? "Could not start the SQLServerAgent Service, error 2186, the service is not responding to the control function"
I can't start the SQLSERVERAGENT for my sql 2k under win 2003 sp1.
SQLServerAgent could not be started (reason: SQLServerAgent cannot start because the instance of the server (MSSQLSERV) is not the expected instance (MSSQLSERVER)).
I can't find any useful information regarding this error. Any one can help?
When my system re-boots SQLServerAgent will no longer start. The following message is sent to the NT Event Log:
(Event ID 103) "SQLServerAgent could not be started (reason: Unable to connect to server; SQLServerAgent cannot start)."
Following this message are two messages from MSSQLServer that read:
(Event ID 19020) "RPC Net-Library listening on: ncalrpc:USBGRSYSTST1[WMSG0000009A.00000001]."
So is this a timing issues? Is MSSQLServer taking too long to completely start before SQLServerAgent is started? If so, is there a way (without hacking through the registry) to make the SQLServerAgent service dependant on the MSSQLServer service?
This startup issue did not exist until an application was installed on the machine that also has several services that auto-start. I noticed that many of these new services are starting at the same time the MSSQLServer service is starting.
We're running sql 7.0 sp1 and both the sqlserver service and sqlserveragent service are running under local system account. For some reason, the sqlserveragent service started failed with "error 2140" - windows nt internal error From event viewer, it saids it has some connection problem.
The sqlserver service has no problem. All knowledge base material related to 6.5 resolution. Would rebuilding the registry entry help ? I tested and rebuilding the registry did not regrant any registry permission, should it ?
wanted to start the sql server agent and i got this error : An Error 1069 - (The service did not start due to logon faliure ) occured wile performing this service operation on SQLServerAgent servcice.
I am getting the following warning message in the event viewer. I have only one schedule in sql. there is no other schedules in task scheduler or database management.
Event Type:Warning Event Source:SQLSERVERAGENT Event Category:Job Engine Event ID:208 Description: SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'' (0xD25A4CDDD1C59E48B4D28F61F0A411B8) - Status: Failed - Invoked on: 2004-08-16 02:30:00 - Message: The job failed. The Job was invoked by Schedule 2 (Schedule 1). The last step to run was step 1 (Step 1).
please i need your help on that sudenly me SQLserveragent stopped and when i try to start it again it give me that error: Event Type:Error Event Source:SQLSERVERAGENT Event Category:Service Control Event ID:103 Date:2/27/2006 Time:11:00:19 AM User:N/A Computer:EGDC2 Description: SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
This may be more of an IT question, but I hope someone here will have the answer.
I am trying to change the "Log on as" for SQLSERVERAGENT NT Service through Windows Registry. I am able to find HKLMSYSTEMCurrentControlSetServicesSQLSERVERAGENTObjectName and change it but I dont know how to specify a password to go with it. If I simply change it to .Administrator (from Local System) it errors out.
Ideally I would like to script this so I can change the logon without having to go though the Services Control Panel or SQL Management Studio.
I have a maintenance plan on SQL2000, that is backing up the database.
It is under Management/SQL Server Agent. When I try to start the job, I got an error, saying: Error 22022 SQLServerAgent is not currently running so it cannot be notified of this action.
But, when I try to start the SQL Server Agent (in Services), it stops directly with the following information: The SQLAgent$Sharepoint service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.
When I try to start it from the SQL Server Service Manager: no reaction at all!
I have successfully installed SQL 2005 on W2K3 Cluster. The problem is that SQL Server Agent won't start. Below are the lines from SQLAGENT.OUT:
2007-03-15 09:40:02 - ! [298] SQLServer Error: 2, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2007-03-15 09:40:02 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00] 2007-03-15 09:40:02 - ! [298] SQLServer Error: 2, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001] 2007-03-15 09:40:02 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start 2007-03-15 09:40:07 - ! [298] SQLServer Error: 2, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2007-03-15 09:40:07 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00] 2007-03-15 09:40:07 - ! [298] SQLServer Error: 2, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001] 2007-03-15 09:40:07 - ! [382] Logon to server '(local)' failed (DisableAgentXPs) 2007-03-15 09:40:08 - ? [098] SQLServerAgent terminated (normally)
I have tried starting Agent with Domain Admin account. No luck. SQL Server is started.
