Impersonation Failing In CLR Proc

Jul 16, 2006

This is driving me nuts, below is the C# for the proc as well as the runtime error upon calling EXEC on it. Any help would be appreciated. Using UNSAFE Permission Set.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Security;

using System.Security.Principal;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure()]

public static void uspExternalConnection()

{

WindowsIdentity newIdentity = null;

WindowsImpersonationContext newContext = null;

try

{

//impersonate the caller

newIdentity = SqlContext.WindowsIdentity;

newContext = newIdentity.Impersonate();

if(newContext != null)

{

using (SqlConnection oConn =

new SqlConnection("Server=.\sqlexpress;" +

"Integrated Security=true;"))

{

SqlCommand oCmd =

new SqlCommand("SELECT * FROM AdventureWorks.HumanResources.Employee", oConn);

oConn.Open();

SqlDataReader oRead =

oCmd.ExecuteReader(CommandBehavior.CloseConnection);

SqlContext.Pipe.Send(oRead);

}

}

else

{

throw new Exception("user impersonation has failed");

}

}

catch (Exception ex)

{

SqlContext.Pipe.Send(ex.Message.ToString());

}

finally

{

if (newContext != null)

{

newContext.Undo();

}

}

}

};

Msg 6522, Level 16, State 1, Procedure uspExternalConnection, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'uspExternalConnection':

System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

System.InvalidOperationException:

at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)

at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)

at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)

at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()

at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()

at Microsoft.SqlServer.Server.SqlContext.get_Pipe()

at StoredProcedures.uspExternalConnection()

View 1 Replies


ADVERTISEMENT

Impersonation Through EXECUTE AS Failing

May 21, 2008

Hello all-
Before I go any further, I have followed http://msdn.microsoft.com/en-us/library/ms188304.aspx as best possible. I am attempting to send mail through a DML trigger. We'll call the database 'DB', and it is owned by a domain account named 'DOMAINAcct'. The trigger simply blocks any CUD operations on a table which we'll call 'Tbl', and sends an email. Hence, it looks something like...

CREATE TRIGGER [dbo].[TR_Tbl_BlockChanges]
ON [dbo].[Tbl]
WITH EXECUTE AS OWNER
INSTEAD OF INSERT,DELETE,UPDATE
AS
EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'AcctMail', @recipients = 'foo@bar.com', @subject = N'CUD operations not allowed on Tbl', @body = N'Blocked'

AcctMail is a valid profile and operates correctly. I have created the DOMAINAcct user in msdb, given it the AUTHENTICATE permission, and added it to the DatabaseMailUserRole. When the trigger fires, according to the article, the security context should switch to dbo (DOMAINAcct), then be successful when attempting to execute the msdb sproc. Instead I get the usual:
Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

Thoughts?

View 4 Replies View Related

Stored Proc Failing To Return Results

Aug 9, 2000

I have a search stored proc which fails to return results when called by more than one user.

I have put selects in the various SPs to trace results and if I call from 3 query windows (executnig each as quickly as I can work the mouse) I get the following:
1st query returns the trace values (including correct count of temp table recs) but no result set
2nd query erturns nothing just "The command(s) completed successfully."
3rd query returns full results.

This seems to be consistent.

We are running SQL Server 7 SP1.
Just upgrading to SP2 to see if that helps.

The main SP calls other SPs to build result sets.
These use quite a few temp tables passed between SPs, parse CSV lists, join different other tables, create a SQL string to exec to do the search and get record IDs to return (no cursors).
The result set is built by a called SP using the temp table with IDs to return.

Anyone know of any problems or can suggest anything to try?

View 3 Replies View Related

Impersonation W/SQL CLR

Nov 7, 2007

I want a user to be able to call a stored procedure, that will call an assembly, that will logon on to another SQL Server, perform some functions (calculations), and return the results. I want the user's credantals passed, NOT the SQL Server Account. So in some research, I created this:

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Security.Principal


Public Class SomeName
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub LinkedServer()
Dim cmd As SqlCommand
Dim dr As SqlDataReader
Dim clientId As WindowsIdentity
Dim impersonatedUser As WindowsImpersonationContext

clientId = SqlContext.WindowsIdentity
impersonatedUser = clientId.Impersonate()



Try
Try
impersonatedUser = clientId.Impersonate()

If impersonatedUser IsNot Nothing Then

' as usual, connection strings shouldn't be hardcoded for production code
Using conn As New SqlConnection( _
"Data Source=SERVER1; Initial Catalog=master; Integrated Security=SSPI")
conn.Open()

cmd = New SqlCommand( _
"SOME QUERY", conn)

dr = cmd.ExecuteReader()

SqlContext.Pipe.Send(dr)

End Using
End If
Finally
If impersonatedUser IsNot Nothing Then
impersonatedUser.Undo()
End If
End Try

Catch ex As Exception
SqlContext.Pipe.Send("Error: " & ex.Message)
End Try
End Sub
End Class

Now the issue is that I get this message when I execute this code with the Impersonation code.

Msg 10312, Level 16, State 49, Procedure spr_SQLServerAccess, Line 0

.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.


When I exclude the impersonation code, everything works, BUT executes under the SQL Server Account.

I have used this code to create the Assembly and Stored Procedure:

-- Register the assembly

CREATE ASSEMBLY SQLServerAccess

FROM 'c:linkedserver.dll'

WITH PERMISSION_SET=EXTERNAL_ACCESS

GO

-- Register the stored-procedure

CREATE PROCEDURE spr_SQLServerAccess

AS

EXTERNAL NAME SQLServerAccess.SomeName.LinkedServer

Any idea's on the error message that is being thrown by SQL WITH the Impersonation code?

View 3 Replies View Related

Wanting To Use Impersonation

May 2, 2007

Hi,

I like to use impersonation using multiple databases and a user with no login.



I'm working with Powerbuilder 10. I can change users using the command Execute Immediate "EXECUTE AS USER = 'username'". Unfortunately, I can't execute the command 'REVERT' from Powerbuilders Execute Immediate command. The Execute Immediate command prefixes the 'REVERT' command with a exec. ie. exec REVERT.



I thought I could encapsulate the REVERT command in a procedure and run the procedure using Execute Immediate. But, I'm new to SQL Server and I'm not sure if I can.



Does anyone know how to solve this problem? Thanks.



TF

View 3 Replies View Related

How Do You Setup Impersonation && SqlExpress

Mar 3, 2008

What's the correct way to set up impersonation & SQLExpress 
Here's the error I'm getting:Cannot open database "aspnetdb" requested by the login. The login failed. Login failed for user '***ASPDATA'.
SQL Express in installed on C:       aspnetdb was set up from aspnet_regsql.exe, on IIS manager - asp.net tab - edit configuration this string is there:     data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true     The aspnetdb is located in C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataThe ASP.Net web is on D:     webconfig file has:  <add name="LocalSQLServer" connectionString="Server=.SQLEXPRESS;Database=aspnetdb;Trusted_Connection=Yes;" />                                 <authentication mode="Windows" />                                <identity impersonate="true" userName=aspdata@xxx.org password="xxx" />
Should I take a copy of aspnetdb and put it in the web app_data folder?Jess

View 1 Replies View Related

Linked Server Impersonation

Sep 29, 2007



I am having a Linked server from SQL 2005 to SQL 2000. Linked server is configured with Local account and remote account "remote_user".

When application hits the linked server, it fails with message "login failed for remote_user".

Any idea how to solve this, i don't have access to remote server.

Regards

View 2 Replies View Related

Using Windows Authentication With Impersonation

Dec 12, 2007



I am installing an application that is a WCF service host running as a windows service under the Network Service account. As part of its configuration I am creating a connectionstring in a config file that will allow the WCF services to access SQL Server. I would like this access to be done using windows authentication not sql server authentication.

connectionString="Server=MYSQLServer;Initial Catalog=MyDatabase;Integrated Security=True;"

So since the windows service is running Logged in under the Network Service account using the above connection string would try to connect to sql server using Network service account. Instead I would like to impersonate another domain account which has has a sql server login and is a user in the database.

Is there a way to configure the connection string to use integrated security but to impersonate another domain user?

Thanks

-- Steven

View 1 Replies View Related

Workgroup -&&> Domain Impersonation

Jul 8, 2006

In the following scenario, I am getting the message 'Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection'.

I am running a Windows Server 2003 with development environment and Sql Server Management Studio in a workgroup on a virtual PC.

My SQL Server 2000 is running on a domain server.

On the virtual Pc I have setup my user login and password to be the same as my domain login and password. Why is the Management Studio not using impersonation and allowing me to connect to the SQL server on the domain?

View 4 Replies View Related

RS2008: Cannot Create Connection When Using Impersonation

Apr 9, 2008

I am testing RS2008 CTP6.
When I view a Adventureworks sample report (e.g. company sales) I get this errormessage when I use the option "Impersonate the authenticated user after a connection has been made to the data source" :


Cannot create a connection to data source 'AdventureWorks'.

Must declare the scalar variable "@ImpersonatedUser".


All other connection options without impersonate works fine.
Any idea what can cause this problem?

Thanks in advance.

View 1 Replies View Related

How To Use CLR Security ..Impersonation To Access External Resources?

Jul 28, 2006

I want to Access External resources inside the CLR Code... But I am getting Security Exception

I have marked Assembly with External Access... here is the way I am doing..

I read articles and MSDN .. everywhere is written to use impersonation like

using (WindowsIdentity id = SqlContext.WindowsIdentity)

{

WindowsImpersonationContext c = id.Impersonate();

//perform operations with external resources and then undo

c.Undo();

}

In above case .. I tried both Windows Authentications and SQL Authentications ...

In case of Windows.. I am have a domain login to logon to my pc, while sql server is at another machine and Active directory is at different machine .. when connect to Database .. it says cannot find user Domainnameuser

and the SqlContext.WindowsIdentity is always null or it has exception User.Toked thew Security exception.



After that .. I tried to user custome Identity .. using IIdentity =GenericIdentity("UserName","Windows");

But there is now difference .. still same exception .. as given below..

[Microsoft.SqlServer.Server.SqlProcedure]

public static void MyProcedure()

{

Process[] p = Process.GetProcessesByName("YPager"); //Yahoo messanger exe .. a process

p[0].kill();

}

A .NET Framework error occurred during execution of user defined routine or aggregate 'MyProcedure': System.Security.SecurityException: Request failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)

at DatFileGenerator.StoredProcedures.'MyProcedure'()

.

No rows affected.

(0 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].['MyProcedure'].



How could I go ahead... what I should do to accompilsh the task...

Kindlly .. suggestions and ideas..

Thanks,

Muna

View 14 Replies View Related

DB Engine :: Linked Servers Cannot Be Used Under Impersonation Without Mapping

Nov 11, 2015

I have a user who is trying to run a job (call an Stored Procedure) which connects to a Linked Server. He can run it OK using EXEC SP_Name but when he runs from the SQL Jobs it gives him the error: Linked servers cannot be used under impersonation without a mapping for the impersonated login.[SQLSTATE 42000] (Error 7437).  The step failed.The Linked Server was setup using another account. Would this be fixed if I add the new user to the Security section of Linked Server without breaking the current configuration? 

View 6 Replies View Related

Executing Stored Procedure Impersonation Question

Jan 29, 2007

As a bit of background first, I'm trying to write a CLR stored proc that will start/stop a Windows Service using the ServiceController class.

The problem I'm having is that the stored proc gets run as NT AUTHORITYNETWORK SERVICE - ie the user the SQLServer Windows Service runs as. This user doesn't have adequate permissions to start/stop a Windows Service (the user only has permission to view the service's status).

The Window's user who is connected to the db - executing the stored proc, does however have adequate permission to start/stop the Windows Service. I'd like to have someway of running the code in the stored proc as if it were this user. If someone could point me in the right direction I'd appreciate it.

View 1 Replies View Related

Accessing Network Shares Using Impersonation And Configuring Delegation

Oct 29, 2007

I'm having trouble trying to access a network share that comes via a UNIX server running SAMBA. In the first case, I'm running on my local workstation (A), connected to a remote server (B), and attempting to access directory information for a path like:

\a0amsimmsworkseaborg argets11as2981

This path is fully accessible by me from the workstation (A) and the server (B). The files and directories below "work" in the above path are also wide open on the UNIX side (meaning r-xr-xrwx permissions). However, if I attempt to do something like this:




Code Block
WindowsIdentity newID = SqlContext.WindowsIdentity;
WindowsImpersonationContext impersonatedUser = newID.Impersonate();
bool sim_dir_exists = false;

try
{
impusername = Environment.UserName;

Directory.GetFiles(mdcfullpath);
}
catch (Exception e)
{
impersonatedUser.Undo();
SqlContext.Pipe.Send("Exception getting data: " + e.ToString());
SqlContext.Pipe.Send("CWD is: " + Directory.GetCurrentDirectory());
SqlContext.Pipe.Send("User is: " + impusername);
}
finally
{
impersonatedUser.Undo();
}




The "GetFiles" fails with the following exception:


Exception getting data: System.UnauthorizedAccessException: Access to the path '\a0amsimmsworkseaborg argets11as2981' is denied.

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)

at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)

at System.IO.Directory.GetFiles(String path)

at StoredProcedures.mdcinfo(Int32 sim_id, String mdc_base)

CWD is: C:WINDOWSsystem32

User is: amsimms

Initial is: dbserver

Interestingly, if I run the procedure directly on the server (B), I do not get the exception. So this seems to be more of a delegation problem. The server B's sql server instance is running as a domain account (dbserver), which has been enabled for delegation and an spn has been set up. Is there something beyond this either with the impersonate or delegation configuration that I need to do in order for this to work?

Thanks,

--Andrew

View 5 Replies View Related

Impersonation Error With File Share Subscriptions In RS 2005

Apr 9, 2008

When publishing to a file share using Reporting Services (no service pack 2 yet) the following error occurs:

Failure writing file NewFile.mhtml : An impersonation error occurred using the security context of the current user.

I have tried publishing to both Windows XP and Server 2000.
The Reporting Services box is Server 2003.
Publishing account is Local Administrator on both Reporting Services and target boxes.
Logon Locally has been granted on both Reporting Services and target boxes.

Any thoughts?

View 7 Replies View Related

Trusted SQL Connection Impersonation And Login Failed For User '(null)'.

May 24, 2006

Hello All,
Login failed for user '(null)'.
I know this issue is all over the forum, however i have not found any posts that help me resolve the issue.
Situation:
I have an ASP.NET 2.0 application hosted currently on XP pro(will be moving to 2003 Server) which connects to a SQL 2000 database that resides on a different server. I have taken the following step to implement my security.
Given my account permissions to the database
Put the following in my web.config
<add name="MyName" connectionString="Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
<authentication mode="Windows" />
<identity impersonate ="true" />
I have set IIS to use integrated authentication and removed anonymous.  The application works when run from the web server but not when run from a remote machine.
Thank you for any asistance,
George 

View 1 Replies View Related

Impersonation Mode Issue: Multiple Analysts Requiring Separate AS DBs For Security Model

Nov 15, 2006

Hello--

We have a current situation where analysts will be modeling a variety of problems, all stemming from the same source data (stored in a SQL-Server 2005 relational database).

Analysts that work on the same problem will only have access to:

- A sandbox relational database (which contains views into the same source database). The analyst is db_owner of the sandbox database, so she/he can create data transformations required, etc. The sandbox database contains views to the source database, but the analyst only has read-access to the specific data elements needed from the source DB. So, they are very restricted w.r.t. the source database, but are db_owners of their sandbox relational databases. Note that the analyst will connect to the database via Windows Authentication.

- An Analysis Services sandbox database to use for their modeling, etc. In this AS sandbox db, we've created a role called "Administrator" and checked the permissions: Full control (Administrator), Process database, and Read definition. The analyst's windows account is the "user" associated with this role.

Also, in this situation, the SQL Server 2005 Relational Engine and Analysis Services are running on a single machine. The goal of this security model is to provide analysts with the ability to work in their "workspaces" (both SQL and AS), but not to see other analysts work, etc.

I'm running into a problem when trying to build models using this security model by doing the following:
- Running Visual Studio
- Selecting File -> Open -> Analysis Services Database and choosing the AS DB that I have access to (this is the only one that appears in the drop-down, after specifying the AS server).
- I've created a data source pointing to the relational sandbox DB.
- I've created a data source view choosing the table/view needed for the case table.
- I created a mining structure with a decision tree model

When I process the mining structure, I'm getting the following errors:

- If the data source Impersonation is "Default" -- the error is "The datasource, '<DS name>', contains an ImpersonationMode that is not supported for processing operations."

- If the data source Impersonation is "Use the credentials of the current user" -- the error is the same as "Default" above -- "The datasource, '<DS name>', contains an ImpersonationMode that is not supported for processing operations."

- If I change the data source Impersonation to "Use the service account" and select "OK" in the "Data Source Designer" window, and error comes up with message: "The ImpersonationInfo for '<DS name>' contains an ImpersonationMode that can only be used by a server administrator.

Any suggestions or pointers to help implement this security model to provide analysts with AS and SQL Relational resources for their modeling?


Thanks,

- Paul

View 1 Replies View Related

Stored Proc Question : Why If Exisits...Drop...Create Proc?

Jun 15, 2006

Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK

View 3 Replies View Related

ASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer

Feb 23, 2007

I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.

View 1 Replies View Related

Processing The Resultset Of Another Proc From A Proc

Apr 8, 2004

Is it possible to retrieve the resultset of a stored procedure from another procedure in sql server 2000.
Basically I am calling proc2 from the inside of proc1.
proc2 returns 2 resultsets. I want to process these two resultsets
from within proc1.

If its possible , please provide sample code.

thanks in advance,
Alok.

View 1 Replies View Related

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT  @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND  Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END 
 

View 3 Replies View Related

Resultset Of Proc In Another Proc

Oct 4, 2005

Hello,i want to use the result set from a stored proc in another stored proc, forexample:create stored procedure proc1 (@x int) asdeclare @tbl (y1 int, y2 int)insert into @tbl values(@ * @x, @x * @x * @x)select * from @tblGO--create stored procedure proc2 (@x int) asdeclare @tbl (y1 int, y2 int)while @x > 0 begininsert into @tbl select (exec proc1 @x) <-- this is my problemset @x = @x - 1endselect * from @tblGO--I know i could use output parameters. But i want to know if something ispossible with SQL-Server?thanks,Helmut

View 4 Replies View Related

Calling A Proc From A Proc

Jan 10, 2007

I'm having problems calling my second proc (MyProcedure2) from within anexisting proc. MyProcedure2 does not seeem to fire this way. My code isbelow. Help appreciated.Thanks,TrevorALTER procedure dbo.MyProcedure1(@newsletterid int)ASSET NOCOUNT ON-- Return Subscribersdeclare @howmany intset @howmany=isnull((select count(subscriberid) from mySubscribers wherenewsletterid=@newsletterid),0)update Mynewsletters set status=3,howmany=@howmany wherenewsletterid=@newsletteridselect @howmanyexec MyProcedure2*** Sent via Developersdex http://www.developersdex.com ***

View 4 Replies View Related

Stored Proc - Calling A Remote Stored Proc

Aug 24, 2006

I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563

This works fine on my local server:

Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName

This does not work (Attempting to execute a remote stored proc named 'Data_Add':

Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'

When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.

Could anyone shed some light on what I need to do to get this to work?

Thanks - Amos.

View 3 Replies View Related

Bcp Failing

Jan 4, 2001

i ran it from the command prompt. I used my nt account which belongs to the domain admin nt group. my account does have sql access as sa.

also on one of the servers all jobs are failing with the following message - Unable to Connect to Sql Server (local). The nt log records the error that the specific user sqlexec (this is the account on which sql executive runs) is not defined as a valid user of a trusted sql server connection. I am not able to change the security setting on this server using EM nor am i able to use the sql security manager. I get an access denied error. What is the workaround for this problem? Will stopping and restarting the sql service help?
------------

How did you run bcp? In dos prompt or as sql job? Which nt account did you run bcp under? Did you grant sql access for that nt account?


------------
aruna at 1/3/01 4:39:49 PM

Subject:
From:
Date: bcp failing (reply)
Ray Miao ()
1/3/01 4:15:33 PM

yes i did. it still gives me the same error - 18452 error not associated with a trusted connection
-----------------

Did you enable mixed login mode on the server?

------------
aruna at 1/3/01 2:55:59 PM


hello ray

It still does not work. I granted SA rights for the nt group via sql security manager. For one of the servers i get the following error message -
This sql server does not support Windows NT SQL Server Security stored procedures.


--------------
From:
Date: bcp over trusted connections failing (reply)
Ray Miao ()
1/3/01 12:51:50 PM

Use security manager to grant access for nt account.


------------
aruna at 1/3/01 11:59:49 AM

i am attempting to bcp using the -T (trusted connection) option in sql 6.5. the login security mode is set to integrated. the bcp is however failing with msg 18452 error not associated with a trusted connection. why is this happening? i do not want to hardcode the sa password in the bcp command.

thanks

View 1 Replies View Related

Help With Failing Job

May 10, 2006

I have an excutable on the c drive and I have created a job to run that excutable

In the Job
C:Folderjob.exe BA

The job was running until we had a power outage. Now I can get it to run with a scheduled job, the only way I can get it to run is typing it on the command line. I have tried droping and recreating this job but nothing works.

The error is: The stip did not generate any out put.

Do I need to troubleshoot the excutable which is a whole other beast.

Any help would be great.

Thanks

View 4 Replies View Related

SQL Job Failing

Apr 19, 2004

We have a sqljob keep failing if we set the ownership of the job other than 'sa'. Any idea why this fails?

View 6 Replies View Related

Job Failing

Aug 22, 2005

Hi,

View 6 Replies View Related

Job Failing

Apr 9, 2007

I have a scheduled job on a SQL 2000 database which is failing. Here is the error message :



The job failed. Unable to determine if the owner (cacisnasir) of job Integrity Checks Job for DB Maintenance Plan 'IDS' has server access (reason: Could not obtain information about Windows NT group/user 'cacisnasir'. [SQLSTATE 42000] (Error 8198)).



I am the SA on the instance. I wonder why would I be getting this error message? I am able to logon to this instance and browse and change things. So clearly it recognizes me. But when I run the job it fails. Wonder why? my SQL Server version is 8.0.

View 6 Replies View Related

[C#] Failing To Use SCOPY_IDENTITY()

Dec 20, 2004

Dear Reader,

Currently I am building an application for a theme park where I work as a trainee for school, one project for me is to rebuild all the hundreds of databases into a few sql driven application's. Now I got a problem whit the use of SCOPE_IDENTITY(). Because the data has to be correct before inserting it into the database I use the transact features of .NET and I create 1 SQL string wich I dump in that method. The problem is that I can't be able to use the value of SCOPE_IDENTITY() for some reason, maybe you guys see a mistake in the actual (dynamic) query:
Here is the query built up by my program to write the data (of a single form) into the database:

DECLARE @OID int;
INSERT INTO Medisch (med_za_ID, med_WeekNr, med_Enen, med_Bijzonderheden, med_AfwijkendGedrag, med_SexGedrag, med_GemAdemhaling, med_GemHoesten, med_Temperatuur, med_Conditie, med_BloedGeprikt, med_Cupje, med_BasisVis, med_Eetlust, med_GemGewicht)VALUES(3,1123,,'','','',,,,'','False','False',45,'',);
SELECT @OID = SCOPE_IDENTITY();
INSERT INTO Medisch_Medicijnen_Details (mmd_mmt_ID, mmd_med_ID, mmd_Hoeveelheid, mmd_Aantal) VALUES(@OID, 2,23, 23 );

Everything else works unless the SCOPE_IDENTITY() things.
I hope someone can help me out fixing this mistake.
Tnx in advanced,

Grz.
Stefan

View 10 Replies View Related

DTS Failing(very Urgent)

Feb 13, 2002

Sql Guru's,

I have some DTS packages some times failing.one day sucess and next day it's failing. The following error showing.
DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147217900 (80040E14) Error string: OLE DB provider 'SQLOLEDB' reported an error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 7399 (1CE7) Error string: OLE DB provider 'SQLOLEDB' reported an error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7312 (1C90) Error string: [OLE/DB provider returned message: Timeout expired] Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRu... Process Exit Code 1. The step failed.

Any body help me. This is very urgent

View 2 Replies View Related

Login Failing....

Apr 24, 2001

this is the message that i'm getting and i dont know what to do so that i can access my SQL databases thru cold fusion:

ODBC Error Code = 37000(Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

i didnt have any problems with this database until i moved it over to another SQL server and tried the cold fusion front end to it. i dont know what to do now.

View 1 Replies View Related

Who Is Failing SA Login

Feb 16, 2001

I have inherited the task of setting some standards for SQL Server setup and usage in my company. Use of SA with and without a password was rampant. As I get DTS jos and VB code changed to use another account I have been securing the SA account with a password that no one uses. I now get a multitude of failed logins for the SA account on multiple systems by people trying to logon as SA, not jobs. Is there any way to generate an error message that will pass the host PC or server, or network ID of the user trying to login with the SA account?

Thanks in advance.
Bill

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved