Access to the remote server is denied because the current security context is not trusted.

Hello,

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.

Thank you for your help on this - Amos.

I assume you are using a Windows principal for the EXECUTE AS statement, correct? If that is the case, I think I know the problem. When using EXECUTE AS <windows_principal> there is no real authentication for the Windows user:

* If you have a OS older than Windows 2003, the Windows token would really be valid for SQL Server and not a real Windows token

* if you are using Windows 2003, and Kerberos is available, the system should use a S4USelf token and these type of tokens are, as far as I understand these tokens are restricted, and out of the box you should not be able to use them on another machine.

If your scenario falls under the S4USelf token, it may be possible to use delegation and use this token on the remote server (during the remote SP call), but I would personally not recommend it.I would prefer to suggest changing the EXECUTE AS clause to use a SQL principal (SQL authentication should work).

Remember that for remote calls to work with EXECUTE AS, it is necessary to trust the impersonated token on the server, turning on the TRUSTWORTHY bit on the source DB (the DB where the local SP resides) and making sure the DBO has AUTHENTICATE SERVER permission (if DBO is a member of sysadmin, this permission is implicitly granted).

Let us know if this information was of any help or if you have additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

Thank you for taking the time to answer. I am getting a bit desperate for an answer!

First let me say that I don't have a great deal of experience in this area (security). Therefore, I might be asking some pretty dumb questions. My first question is this. On my remote stored proc, do I have to use 'WITH EXECUTE AS'? I would rather not if I don't have to. I cannot get this to work whether I use it or not. So, let's start there. Can I get this to work with the Execute As?

Amos.

|||

You don’t have to use EXECUTE AS on the remote SP unless you want to. I am assuming you want to execute always under the exactly same principal (on the local DB) and that’s why you used execute as on the local SP, but as you will always connect as the same principal on the remote machine, using EXECUTE AS will be of little value.Even for the local SP, you don’t need EXECUTE AS unless you want to always use the same principal (i.e. use it as a proxy) to connect to the remote machine and execute the remote SP.

If you prefer, you can describe the problem you want to solve and I will do my best to help you find a solution.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

You are giving me too much credit :). I only used Execute As to try to get this to work. So, let's go under the assumption I don't need it. Here is my current setup now:

The remote stored proc does not have 'Execute As'|||

I can see one mistake here: the trustworthy bit is enabled on the remote server, but it should be for the local server. Also make sure that if the DBO for the local SP DB is not a member of sysadmin, to grant AUTHENTICATE SERVER to the DBO login.

The TW bit + AUTEHNTICATE SERVER will tell the local server that the impersonated context is valid across the SQL Server instance, and only then it can be used in remote calls.

BTW. What is the impersonated context (EXECUTE AS clause) being used? Is it a Windows principal or a SQL principal?

|||

Raul,

I don't know how to thank you! That was it. My local database was not set to Trustworthy. I actually thought about changing this yesterday but it didn't make any sense to me to do that so I didn't try it.

Amos.

|||

No problem, I am glad I was able to help you resolve this problem.

Please let us know if you have any further questions or feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

For additional information on the TRUSTWORTHY bit, see the following whitepaper:

http://msdn2.microsoft.com/en-us/library/ms188304.aspx

Thanks
Laurentiu

|||

Hi,

Great post, only it doesn't fix the problem that I am having. All the criteria above is true to my situation and I have run through the checklist described, but I still receve the "Access to the remote server is denied because the current security context is not trusted" error message.

Is there anything else that I can try?

Thanks

MIke

|||

thats got it.

The Authenticator of the trust is the DBO of the database, if the DBO is not a member of the target db (and trusted) then the error occurs.

By updating the source DBO to one that is trusted I now have working links.

Thanks for all your help peeps.

Mike

|||

Can you explain the solution in more detail?

I am having a similar problem:

I have a Linked server on a SQL 2005 database that connects to an Ingres DB

I am able to run stored procedures manually in management studio but when I schedule a job to run these procedures I get a similar error - Security context is not trusted.

I've set the local database to have Trustworthy bit on. But how can I set the remote server via the linked server object with the Trustworthy bit. Also I have the Security Context on the linked server - with the option - Connections be made with the following security context - and I have provided a username and password that has access to the remote database.

Thanks

Sg

|||

IIRC, I had to set both the local database and the remote database to Trustworthy. In addition, make sure the "RPC" and "RPC Out" options on the Linked Server defintion are set to True.

Amos.

Access to the remote server is denied because the current security context is not trusted.

Hello,

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.

Thank you for your help on this - Amos.

I assume you are using a Windows principal for the EXECUTE AS statement, correct? If that is the case, I think I know the problem. When using EXECUTE AS <windows_principal> there is no real authentication for the Windows user:

* If you have a OS older than Windows 2003, the Windows token would really be valid for SQL Server and not a real Windows token

* if you are using Windows 2003, and Kerberos is available, the system should use a S4USelf token and these type of tokens are, as far as I understand these tokens are restricted, and out of the box you should not be able to use them on another machine.

If your scenario falls under the S4USelf token, it may be possible to use delegation and use this token on the remote server (during the remote SP call), but I would personally not recommend it.I would prefer to suggest changing the EXECUTE AS clause to use a SQL principal (SQL authentication should work).

Remember that for remote calls to work with EXECUTE AS, it is necessary to trust the impersonated token on the server, turning on the TRUSTWORTHY bit on the source DB (the DB where the local SP resides) and making sure the DBO has AUTHENTICATE SERVER permission (if DBO is a member of sysadmin, this permission is implicitly granted).

Let us know if this information was of any help or if you have additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

Thank you for taking the time to answer. I am getting a bit desperate for an answer!

First let me say that I don't have a great deal of experience in this area (security). Therefore, I might be asking some pretty dumb questions. My first question is this. On my remote stored proc, do I have to use 'WITH EXECUTE AS'? I would rather not if I don't have to. I cannot get this to work whether I use it or not. So, let's start there. Can I get this to work with the Execute As?

Amos.

|||

You don’t have to use EXECUTE AS on the remote SP unless you want to. I am assuming you want to execute always under the exactly same principal (on the local DB) and that’s why you used execute as on the local SP, but as you will always connect as the same principal on the remote machine, using EXECUTE AS will be of little value.Even for the local SP, you don’t need EXECUTE AS unless you want to always use the same principal (i.e. use it as a proxy) to connect to the remote machine and execute the remote SP.

If you prefer, you can describe the problem you want to solve and I will do my best to help you find a solution.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

You are giving me too much credit :). I only used Execute As to try to get this to work. So, let's go under the assumption I don't need it. Here is my current setup now:

The remote stored proc does not have 'Execute As'|||

I can see one mistake here: the trustworthy bit is enabled on the remote server, but it should be for the local server. Also make sure that if the DBO for the local SP DB is not a member of sysadmin, to grant AUTHENTICATE SERVER to the DBO login.

The TW bit + AUTEHNTICATE SERVER will tell the local server that the impersonated context is valid across the SQL Server instance, and only then it can be used in remote calls.

BTW. What is the impersonated context (EXECUTE AS clause) being used? Is it a Windows principal or a SQL principal?

|||

Raul,

I don't know how to thank you! That was it. My local database was not set to Trustworthy. I actually thought about changing this yesterday but it didn't make any sense to me to do that so I didn't try it.

Amos.

|||

No problem, I am glad I was able to help you resolve this problem.

Please let us know if you have any further questions or feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

For additional information on the TRUSTWORTHY bit, see the following whitepaper:

http://msdn2.microsoft.com/en-us/library/ms188304.aspx

Thanks
Laurentiu

|||

Hi,

Great post, only it doesn't fix the problem that I am having. All the criteria above is true to my situation and I have run through the checklist described, but I still receve the "Access to the remote server is denied because the current security context is not trusted" error message.

Is there anything else that I can try?

Thanks

MIke

|||

thats got it.

The Authenticator of the trust is the DBO of the database, if the DBO is not a member of the target db (and trusted) then the error occurs.

By updating the source DBO to one that is trusted I now have working links.

Thanks for all your help peeps.

Mike

|||

Can you explain the solution in more detail?

I am having a similar problem:

I have a Linked server on a SQL 2005 database that connects to an Ingres DB

I am able to run stored procedures manually in management studio but when I schedule a job to run these procedures I get a similar error - Security context is not trusted.

I've set the local database to have Trustworthy bit on. But how can I set the remote server via the linked server object with the Trustworthy bit. Also I have the Security Context on the linked server - with the option - Connections be made with the following security context - and I have provided a username and password that has access to the remote database.

Thanks

Sg

|||

IIRC, I had to set both the local database and the remote database to Trustworthy. In addition, make sure the "RPC" and "RPC Out" options on the Linked Server defintion are set to True.

Amos.

Access to the remote server is denied because the current security context is not trusted.

Hello,

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.

Thank you for your help on this - Amos.

I assume you are using a Windows principal for the EXECUTE AS statement, correct? If that is the case, I think I know the problem. When using EXECUTE AS <windows_principal> there is no real authentication for the Windows user:

* If you have a OS older than Windows 2003, the Windows token would really be valid for SQL Server and not a real Windows token

* if you are using Windows 2003, and Kerberos is available, the system should use a S4USelf token and these type of tokens are, as far as I understand these tokens are restricted, and out of the box you should not be able to use them on another machine.

If your scenario falls under the S4USelf token, it may be possible to use delegation and use this token on the remote server (during the remote SP call), but I would personally not recommend it.I would prefer to suggest changing the EXECUTE AS clause to use a SQL principal (SQL authentication should work).

Remember that for remote calls to work with EXECUTE AS, it is necessary to trust the impersonated token on the server, turning on the TRUSTWORTHY bit on the source DB (the DB where the local SP resides) and making sure the DBO has AUTHENTICATE SERVER permission (if DBO is a member of sysadmin, this permission is implicitly granted).

Let us know if this information was of any help or if you have additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

Thank you for taking the time to answer. I am getting a bit desperate for an answer!

First let me say that I don't have a great deal of experience in this area (security). Therefore, I might be asking some pretty dumb questions. My first question is this. On my remote stored proc, do I have to use 'WITH EXECUTE AS'? I would rather not if I don't have to. I cannot get this to work whether I use it or not. So, let's start there. Can I get this to work with the Execute As?

Amos.

|||

You don’t have to use EXECUTE AS on the remote SP unless you want to. I am assuming you want to execute always under the exactly same principal (on the local DB) and that’s why you used execute as on the local SP, but as you will always connect as the same principal on the remote machine, using EXECUTE AS will be of little value.Even for the local SP, you don’t need EXECUTE AS unless you want to always use the same principal (i.e. use it as a proxy) to connect to the remote machine and execute the remote SP.

If you prefer, you can describe the problem you want to solve and I will do my best to help you find a solution.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

You are giving me too much credit :). I only used Execute As to try to get this to work. So, let's go under the assumption I don't need it. Here is my current setup now:

The remote stored proc does not have 'Execute As'|||

I can see one mistake here: the trustworthy bit is enabled on the remote server, but it should be for the local server. Also make sure that if the DBO for the local SP DB is not a member of sysadmin, to grant AUTHENTICATE SERVER to the DBO login.

The TW bit + AUTEHNTICATE SERVER will tell the local server that the impersonated context is valid across the SQL Server instance, and only then it can be used in remote calls.

BTW. What is the impersonated context (EXECUTE AS clause) being used? Is it a Windows principal or a SQL principal?

|||

Raul,

I don't know how to thank you! That was it. My local database was not set to Trustworthy. I actually thought about changing this yesterday but it didn't make any sense to me to do that so I didn't try it.

Amos.

|||

No problem, I am glad I was able to help you resolve this problem.

Please let us know if you have any further questions or feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

For additional information on the TRUSTWORTHY bit, see the following whitepaper:

http://msdn2.microsoft.com/en-us/library/ms188304.aspx

Thanks
Laurentiu

|||

Hi,

Great post, only it doesn't fix the problem that I am having. All the criteria above is true to my situation and I have run through the checklist described, but I still receve the "Access to the remote server is denied because the current security context is not trusted" error message.

Is there anything else that I can try?

Thanks

MIke

|||

thats got it.

The Authenticator of the trust is the DBO of the database, if the DBO is not a member of the target db (and trusted) then the error occurs.

By updating the source DBO to one that is trusted I now have working links.

Thanks for all your help peeps.

Mike

|||

Can you explain the solution in more detail?

I am having a similar problem:

I have a Linked server on a SQL 2005 database that connects to an Ingres DB

I am able to run stored procedures manually in management studio but when I schedule a job to run these procedures I get a similar error - Security context is not trusted.

I've set the local database to have Trustworthy bit on. But how can I set the remote server via the linked server object with the Trustworthy bit. Also I have the Security Context on the linked server - with the option - Connections be made with the following security context - and I have provided a username and password that has access to the remote database.

Thanks

Sg

|||

IIRC, I had to set both the local database and the remote database to Trustworthy. In addition, make sure the "RPC" and "RPC Out" options on the Linked Server defintion are set to True.

Amos.

Access to the remote server is denied because the current security context is not trusted.

Hi all,
What I did:
Created a SQL Job that accesses another database on some other server.
I get the error:
Access to the remote server is denied because the current security
context is not trusted. [SQLSTATE 42000] (Error 15274).
I have the remote server as the linked server to my local server.
The login account used for the job step is present on both the servers
(with the role sysadmin)
Any pointers?
All help would be appreciated.
Thanks in anticipation,
Vivek Balagangadharan
Software Engineer,
Fast Track Software Services,
India
Forgot to mention one thing:
I tried executing the same query through my management studio with the
same login, and it works great.
It also works when i try using it from my web application.
But, it doesnt work when i use a SQL job
Thanks,
Vivek Balagangadharan
Software Engineer,
Fast Track Software Services,
India
|||Hi Vivek,
How is security defined on that linked server? Are you using Intergrated
Security (Windows account) or are you using SQL server authentication.
If you are using Intergrated security, then make sure that the account
that runs your SQL Server Agent service has proper rights to the linked
server (is Trusted for delegation).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> Hi all,
> What I did:
> Created a SQL Job that accesses another database on some other server.
> I get the error:
> Access to the remote server is denied because the current security
> context is not trusted. [SQLSTATE 42000] (Error 15274).
> I have the remote server as the linked server to my local server.
> The login account used for the job step is present on both the servers
> (with the role sysadmin)
> Any pointers?
> All help would be appreciated.
> Thanks in anticipation,
> Vivek Balagangadharan
> Software Engineer,
> Fast Track Software Services,
> India
>
|||Thanks Steen.
I am using SQL authentication and my user is present on both the DB
servers with the role sysadmin.
Now, both these DB servers are on different domains.
But, that still does not explain why it runs from the studio, but not
from the SQL job.
Thanks,
Vivek Balagangadharan
|||And to provide you with more info on the linked server setting, i just
"scripted" the create query for this.
/****** Object: LinkedServer [DB Server 2] Script Date: 06/22/2007
13:02:52 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'DB Server 2',
@.srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is
changed with #### */
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DB Server
2',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'l ogin1',@.rmtpassword='####'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DB Server
2',@.useself=N'False',@.locallogin=N'login1',@.rmtuse r=N'login1',@.rmtpassword='####'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'rpc', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2', @.optname=N'rpc
out', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2', @.optname=N'use
remote collation', @.optvalue=N'true'
|||Hi
I can see that your script creates 2 linkedserver logins where one of
them has a locallogin specified. I'd try to create the linked server
without a local login specified and see if that works (or just drop the
login with a local login specified).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> And to provide you with more info on the linked server setting, i just
> "scripted" the create query for this.
> /****** Object: LinkedServer [DB Server 2] Script Date: 06/22/2007
> 13:02:52 ******/
> EXEC master.dbo.sp_addlinkedserver @.server = N'DB Server 2',
> @.srvproduct=N'SQL Server'
> /* For security reasons the linked server remote logins password is
> changed with #### */
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DB Server
> 2',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'l ogin1',@.rmtpassword='####'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DB Server
> 2',@.useself=N'False',@.locallogin=N'login1',@.rmtuse r=N'login1',@.rmtpassword='####'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'collation compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'data access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'dist', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'pub', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'rpc', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2', @.optname=N'rpc
> out', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'sub', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'connect timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'collation name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'lazy schema validation', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'query timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2', @.optname=N'use
> remote collation', @.optvalue=N'true'
>
|||Thanks Steen.
Yes, initially i had not created any linked server login. But, the job
started failing. That is when i created a linked server login, but
that didn't help either.
I even tried to impersonate a login (since the login was available on
both the servers).
Even forced the connections to use a fixed context (by specifying a
remote server login credential)
This did not work either.
The question that is bothering me is, why is it that the context is
trusted when i use the management studio, but not trusted when i use a
SQL job? (though i use the same login)
Thanks,
Vivek Balagangadharan
|||hmmmm...that sounds a bit strange.
What does the job/script actually do? Could it be that it is trying to
do something on the local/source server that the job hasn't got access
to and that's the error you see rather than an error on the remote
server. I know the error meesage indicates something else, but you never
know..:-).
From where are you executing the job? If you are starting it from your
local PC, could you then try to log on to the server (with e.g. Remote
Desktop) and try to start it from there?
Regards
Steen Schlter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> Thanks Steen.
> Yes, initially i had not created any linked server login. But, the job
> started failing. That is when i created a linked server login, but
> that didn't help either.
> I even tried to impersonate a login (since the login was available on
> both the servers).
> Even forced the connections to use a fixed context (by specifying a
> remote server login credential)
> This did not work either.
> The question that is bothering me is, why is it that the context is
> trusted when i use the management studio, but not trusted when i use a
> SQL job? (though i use the same login)
> Thanks,
> Vivek Balagangadharan
>
|||Thanks for the reply Steen.
Well, all the job has/should do, is a simple "select count(*) from [DB
Server 2].[DB_name].[dbo].[table_name]" query.
In my job step, I have specified to "run as user" login1, which is
present on both the DBs and belongs to the sysadmin role group.
I was initially running the job from my management studio (which is
not the server which has the job)
Then, I moved to the local server which actually had the SQL Server
agent, created the job all over again (the owner being login1). But,
no success.
I hope I have answered your queries. Let me know if there is some
confusion.
Thanks,
Vivek Balagangadharan
|||Have you tried running the job without specifying a proxy account (Run
As account)?
Regards
Steen Schlter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> Thanks for the reply Steen.
> Well, all the job has/should do, is a simple "select count(*) from [DB
> Server 2].[DB_name].[dbo].[table_name]" query.
> In my job step, I have specified to "run as user" login1, which is
> present on both the DBs and belongs to the sysadmin role group.
> I was initially running the job from my management studio (which is
> not the server which has the job)
> Then, I moved to the local server which actually had the SQL Server
> agent, created the job all over again (the owner being login1). But,
> no success.
> I hope I have answered your queries. Let me know if there is some
> confusion.
> Thanks,
> Vivek Balagangadharan
>

Access to the remote server is denied because the current security context is not trusted.

Hi all,
What I did:
Created a SQL Job that accesses another database on some other server.
I get the error:
Access to the remote server is denied because the current security
context is not trusted. [SQLSTATE 42000] (Error 15274).
I have the remote server as the linked server to my local server.
The login account used for the job step is present on both the servers
(with the role sysadmin)
Any pointers?
All help would be appreciated.
Thanks in anticipation,
Vivek Balagangadharan
Software Engineer,
Fast Track Software Services,
IndiaForgot to mention one thing:
I tried executing the same query through my management studio with the
same login, and it works great.
It also works when i try using it from my web application.
But, it doesnt work when i use a SQL job :(
Thanks,
Vivek Balagangadharan
Software Engineer,
Fast Track Software Services,
India|||Hi Vivek,
How is security defined on that linked server? Are you using Intergrated
Security (Windows account) or are you using SQL server authentication.
If you are using Intergrated security, then make sure that the account
that runs your SQL Server Agent service has proper rights to the linked
server (is Trusted for delegation).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> Hi all,
> What I did:
> Created a SQL Job that accesses another database on some other server.
> I get the error:
> Access to the remote server is denied because the current security
> context is not trusted. [SQLSTATE 42000] (Error 15274).
> I have the remote server as the linked server to my local server.
> The login account used for the job step is present on both the servers
> (with the role sysadmin)
> Any pointers?
> All help would be appreciated.
> Thanks in anticipation,
> Vivek Balagangadharan
> Software Engineer,
> Fast Track Software Services,
> India
>|||Thanks Steen.
I am using SQL authentication and my user is present on both the DB
servers with the role sysadmin.
Now, both these DB servers are on different domains.
But, that still does not explain why it runs from the studio, but not
from the SQL job.
Thanks,
Vivek Balagangadharan|||And to provide you with more info on the linked server setting, i just
"scripted" the create query for this.
/****** Object: LinkedServer [DB Server 2] Script Date: 06/22/2007
13:02:52 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'DB Server 2',
@.srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is
changed with #### */
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DB Server
2',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'login1',@.rmtpassword='####'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DB Server
2',@.useself=N'False',@.locallogin=N'login1',@.rmtuser=N'login1',@.rmtpassword='####'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'rpc', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2', @.optname=N'rpc
out', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
@.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'DB Server 2', @.optname=N'use
remote collation', @.optvalue=N'true'|||Hi
I can see that your script creates 2 linkedserver logins where one of
them has a locallogin specified. I'd try to create the linked server
without a local login specified and see if that works (or just drop the
login with a local login specified).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> And to provide you with more info on the linked server setting, i just
> "scripted" the create query for this.
> /****** Object: LinkedServer [DB Server 2] Script Date: 06/22/2007
> 13:02:52 ******/
> EXEC master.dbo.sp_addlinkedserver @.server = N'DB Server 2',
> @.srvproduct=N'SQL Server'
> /* For security reasons the linked server remote logins password is
> changed with #### */
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DB Server
> 2',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'login1',@.rmtpassword='####'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DB Server
> 2',@.useself=N'False',@.locallogin=N'login1',@.rmtuser=N'login1',@.rmtpassword='####'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'collation compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'data access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'dist', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'pub', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'rpc', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2', @.optname=N'rpc
> out', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'sub', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'connect timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'collation name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'lazy schema validation', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2',
> @.optname=N'query timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'DB Server 2', @.optname=N'use
> remote collation', @.optvalue=N'true'
>|||Thanks Steen.
Yes, initially i had not created any linked server login. But, the job
started failing. That is when i created a linked server login, but
that didn't help either.
I even tried to impersonate a login (since the login was available on
both the servers).
Even forced the connections to use a fixed context (by specifying a
remote server login credential)
This did not work either.
The question that is bothering me is, why is it that the context is
trusted when i use the management studio, but not trusted when i use a
SQL job? (though i use the same login)
Thanks,
Vivek Balagangadharan|||hmmmm...that sounds a bit strange.
What does the job/script actually do? Could it be that it is trying to
do something on the local/source server that the job hasn't got access
to and that's the error you see rather than an error on the remote
server. I know the error meesage indicates something else, but you never
know..:-).
From where are you executing the job? If you are starting it from your
local PC, could you then try to log on to the server (with e.g. Remote
Desktop) and try to start it from there?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> Thanks Steen.
> Yes, initially i had not created any linked server login. But, the job
> started failing. That is when i created a linked server login, but
> that didn't help either.
> I even tried to impersonate a login (since the login was available on
> both the servers).
> Even forced the connections to use a fixed context (by specifying a
> remote server login credential)
> This did not work either.
> The question that is bothering me is, why is it that the context is
> trusted when i use the management studio, but not trusted when i use a
> SQL job? (though i use the same login)
> Thanks,
> Vivek Balagangadharan
>|||Thanks for the reply Steen.
Well, all the job has/should do, is a simple "select count(*) from [DB
Server 2].[DB_name].[dbo].[table_name]" query.
In my job step, I have specified to "run as user" login1, which is
present on both the DBs and belongs to the sysadmin role group.
I was initially running the job from my management studio (which is
not the server which has the job)
Then, I moved to the local server which actually had the SQL Server
agent, created the job all over again (the owner being login1). But,
no success.
I hope I have answered your queries. Let me know if there is some
confusion.
Thanks,
Vivek Balagangadharan|||Have you tried running the job without specifying a proxy account (Run
As account)?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> Thanks for the reply Steen.
> Well, all the job has/should do, is a simple "select count(*) from [DB
> Server 2].[DB_name].[dbo].[table_name]" query.
> In my job step, I have specified to "run as user" login1, which is
> present on both the DBs and belongs to the sysadmin role group.
> I was initially running the job from my management studio (which is
> not the server which has the job)
> Then, I moved to the local server which actually had the SQL Server
> agent, created the job all over again (the owner being login1). But,
> no success.
> I hope I have answered your queries. Let me know if there is some
> confusion.
> Thanks,
> Vivek Balagangadharan
>|||Thanks, I just tried and it returns me an error that the windows
account is invalid. (Login failed)
That's because the windows account on local server is not available on
remote server.
Thanks,
Vivek Balagangadharan|||That doesn't make sense. When your Linked Server is setup to use a SQl
Server account, then this account needs to exists on the remote server.
When you then use the linked server, it doesn't use any Windows
accounts. I'm accesing a number of remote servers from many different
servers and I'm always using SQL Server authentication for this. Then
there are no Windows/AD account involved. That works fine even to
servers in our DMZ where we (of course) have a seperate domain that
knows nothing about any of our "normal" domains.
Try to set up your linked server to only use SQL Server authentication
and then make sure that the account you are using exists (and with the
same password) on the remote server. Then try to run the query from
QA/Management Studio to verify that it works. Then try to create a job
step that just runs that query and with no Run As/proxy account defined.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> Thanks, I just tried and it returns me an error that the windows
> account is invalid. (Login failed)
> That's because the windows account on local server is not available on
> remote server.
> Thanks,
> Vivek Balagangadharan
>|||Thank you Steen! It works now :D
Actually, it was a combination of a couple of things.
1) Remove the run as 'login1' for the step (which i started trying
after your post :) )
2) Force all the connections to use a fixed context (by specifying
login1 and his password) in the linked server properties.
And I get this message: "Executed as user: NT AUTHORITY\SYSTEM. The
step succeeded."
:) :) :)
Also, in my desperate attempt to get the code working, I had
introduced "Execute as user = 'login1' " in my sql query. I had to do
away with this too.
Thanks for the quick and continuous help.
Cheers,
Vivek Balagangadharan
Software Engineer,
Fast Track Software Services,
India|||Good to hear that it finally worked...:-).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> Thank you Steen! It works now :D
> Actually, it was a combination of a couple of things.
> 1) Remove the run as 'login1' for the step (which i started trying
> after your post :) )
> 2) Force all the connections to use a fixed context (by specifying
> login1 and his password) in the linked server properties.
> And I get this message: "Executed as user: NT AUTHORITY\SYSTEM. The
> step succeeded."
> :) :) :)
> Also, in my desperate attempt to get the code working, I had
> introduced "Execute as user = 'login1' " in my sql query. I had to do
> away with this too.
> Thanks for the quick and continuous help.
> Cheers,
> Vivek Balagangadharan
> Software Engineer,
> Fast Track Software Services,
> India
>

Access to several SQL Servers implementing trusted connection

Being an IT consultant, I have to visit a number of customers having applications running on SQL Servers that implement trusted connection security model. In addition, all of these companies have their own DOMAIN settings, and security implementation.

How can I access to databases without changing the DOMAIN settings of MY notebook? I do not want them to switch to SQL security model, and I do not want to create any security hole in those companies.

What shall I want from the system administrator so that I can login into their domain without changing my domain settings?

Thanks,

EIO.

Hi,

you can store user network crednetials for your account on the notebook which will be used if connecting to a remote server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

May I ask: How?

I understand that:

1. I have to ask all the administrators in those separate companies to create a user account for me, in their local domains, that has access to the SQL Server databases. That is easy.

2. I have to register those separate domain accounts in my notebook (how?)

3. After I connect to the company network and login to my notebook with my own username, those registrations will make me be acquainted with the company network.

Is this correct? And how can I complete Step 2?

Thanks in advance,

EIO.

|||

Yes you can ask those companies to create a standard account with your name and you can access the SQL Server using Enterprise manager by regsitering those instances. I have done this previously without any problem, where the account name (username) should match for authentication.

On Step2 even though it says cannot connect to server you can still register on your EM lists.

access denied using a trusted connection over a VPN

Hi - when i try to run a .NET app over my VPN using ADO.NET, a trusted conne
ction with SQL in mixed mode, i get 'access denied'. when i run the same app
from my local network thru the VPN, no problem..i'm using the same login an
d password for all computer
s(SQL Server is on another computer in the VPN network, which works fine w/t
rusted connection from the VPN server) How can i get access thru my VPN? Tha
nksAfter you have VPN'd in to your network. Test the following commands:
net use \\ServerName\IPC$
ISQL -Stcp:ServerName -E
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I have the exact same problems. No problems when I'm connected to the
network.
When I'm at home using my vpn-client i'm not able to use trusted
connection.
I'm able to use net use \\myserver\ipc$ without any problems.
I have about 50 clients running Windows 2000 Pro SP4 and about 10 of
them are using VPN and 3 of them have the same problem as I.
I think that these errors started to occur sometime in july.
Might this be some Windowsupdate question..?
I use automatic download and install.
Thanks in advance.
lunatiic
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message743307.html