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 through MS Access ADP for everyone?

Hello,
We have recently installed SQL enterprise manager. I was testing the securi
ty to the databases when I realized that if I login to XP under a standard d
omain user account, open MS Access, create an ADP project, then under connec
tions, pick the sql server, I can see and pick any of the databases and then
I can open and view any of the tables in any of the databases. How can thi
s be possible? We are using NT authentication. This user has no account in
SQL and is just a domain user.It's allowed somehow with the security you have implemented
on the server, in the databases. It's not clear what version
or edition of SQL you are running, where the SQL Server
instance is installed - on the network and you are accessing
this over a network? It's not clear what operating system
the SQL Server is running on, is the SQL Server in a domain
or is this actually a workgroup? Did you change any of the
default security settings? Who are members of Local Admins
where SQL Server is installed? What is the status of the
guest account in Windows where SQL is installed?
What databases are you actually accessing and opening
tables? Are these system databases?
-Sue
On Tue, 11 Oct 2005 21:19:32 -0400, "Jack"
<jackhnospam@.jackandjay.com> wrote:

>Hello,
>We have recently installed SQL enterprise manager. I was testing the security to t
he databases when I realized that if I login to XP under a standard domain user acco
unt, open MS Access, create an ADP project, then under connections, pick the sql ser
ver
, I can see and pick any of the databases and then I can open and view any o
f the tables in any of the databases. How can this be possible? We are usi
ng NT authentication. This user has no account in SQL and is just a domain
user.

access sql server in different domain

I just started as a consultant with a company who won't allow my notebook to
join the domain. No biggee - I can understand the security concerns. I am
able to get on the network and can Remote Desktop to the SQL Servers I need
to get to. However, life would be a heck of a lot better if I could map the
servers in EM. The problem is that the only user id I have in SQL is my
Windows id, not a local SQL Server id. And since I'm logged into my
notebook using cached credentials from a different domain, I can't register
the server in EM. I've read an article about using "net use \\<server>\ipc$
/user:<serverdomain>\<userid> <password>" and I was able to successfully run
this in a command window. I just don't really understand what it did for
me - because I still can't connect to the server in EM.
Is it possible to do what I want to do? If a solution is available in
Yukon, that would be acceptable too.
Thanks, Andre
Andre,
you might try to start EM with the "run as" option, but I doubt it will
work. As an alternative you could try using the WebAdmin utility. It's
more limited than EM, but for most tasks it would be sufficient.
You can download it at
http://www.microsoft.com/downloads/d...displaylang=en
After install make sure you copy the updated
WebUIValidation.js file from
WINDOWS\Microsoft.NET\Framework\v1.1.4322
\ASP.NETClientFiles into {SQL Web Administrator
installation path}\Web\aspnet_client\system_web\1_1_4322\
otherwise the programm hangs when you click the 'login' button.
Hope this helps
M
|||Sorry, me again, must be the lack of coffee, but why don't you just
register the server using your SQL login ? Even though the server is in
a different domain this should work as long as your machine can resolve
the name.
M
|||Hi,
as suggested try using sql login, or you could add / map yr windows login to
sql fixed role so that it will works for you.
regards
|||I don't have a SQL login - only a Windows login that has been granted access
to SQL.
Andre
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1129188096.681173.270080@.g49g2000cwa.googlegr oups.com...
> Sorry, me again, must be the lack of coffee, but why don't you just
> register the server using your SQL login ? Even though the server is in
> a different domain this should work as long as your machine can resolve
> the name.
> M
>
|||I tried to use the WebAdmin utility but I'm not getting it to work. Even
though I copied the file you mentioned, I'm never able to connect to sql -
even my local installation.
And even if I was able to get that to work, it still looks like the only
ways of connecting is using trusted or sql credentials. Unless I can trick
it by selecting sql credentials then typing in domain\username, I'm not sure
this would help me login to a sql server in another domain.
Any tips you have are appreciated.
Thanks, Andre
|||Hi,
are you able to login and do work using QA !? what type of work you wants
to accomplish
Regards
|||> are you able to login and do work using QA !? what type of work you
> wants
> to accomplish
No, I'm not able to. I want to register EM, be able to login to QA, and run
Profiler. I don't have a sql login and am not in the domain - so I can't
use trusted logins.
Andre
|||Hi,
then you must ensure that you have proper privillages ,
using sp_adduser to add your users to fixeddb role,
to ensure the associated role for yr user get script from :
http://www.sql-server-performance.co...TOPIC_ID=10504
HTH
Regards

access sql server in different domain

I just started as a consultant with a company who won't allow my notebook to
join the domain. No biggee - I can understand the security concerns. I am
able to get on the network and can Remote Desktop to the SQL Servers I need
to get to. However, life would be a heck of a lot better if I could map the
servers in EM. The problem is that the only user id I have in SQL is my
Windows id, not a local SQL Server id. And since I'm logged into my
notebook using cached credentials from a different domain, I can't register
the server in EM. I've read an article about using "net use \\<server>\ipc$
/user:<serverdomain>\<userid> <password>" and I was able to successfully run
this in a command window. I just don't really understand what it did for
me - because I still can't connect to the server in EM.
Is it possible to do what I want to do? If a solution is available in
Yukon, that would be acceptable too.
Thanks, AndreAndre,
you might try to start EM with the "run as" option, but I doubt it will
work. As an alternative you could try using the WebAdmin utility. It's
more limited than EM, but for most tasks it would be sufficient.
You can download it at
http://www.microsoft.com/downloads/details.aspx?FamilyId=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en
After install make sure you copy the updated
WebUIValidation.js file from
WINDOWS\Microsoft.NET\Framework\v1.1.4322
\ASP.NETClientFiles into {SQL Web Administrator
installation path}\Web\aspnet_client\system_web\1_1_4322\
otherwise the programm hangs when you click the 'login' button.
Hope this helps
M|||Sorry, me again, must be the lack of coffee, but why don't you just
register the server using your SQL login ? Even though the server is in
a different domain this should work as long as your machine can resolve
the name.
M|||Hi,
as suggested try using sql login, or you could add / map yr windows login to
sql fixed role so that it will works for you.
regards|||I don't have a SQL login - only a Windows login that has been granted access
to SQL.
Andre
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1129188096.681173.270080@.g49g2000cwa.googlegroups.com...
> Sorry, me again, must be the lack of coffee, but why don't you just
> register the server using your SQL login ? Even though the server is in
> a different domain this should work as long as your machine can resolve
> the name.
> M
>|||I tried to use the WebAdmin utility but I'm not getting it to work. Even
though I copied the file you mentioned, I'm never able to connect to sql -
even my local installation.
And even if I was able to get that to work, it still looks like the only
ways of connecting is using trusted or sql credentials. Unless I can trick
it by selecting sql credentials then typing in domain\username, I'm not sure
this would help me login to a sql server in another domain.
Any tips you have are appreciated.
Thanks, Andre|||Hi,
are you able to login and do work using QA !? what type of work you wants
to accomplish
Regards|||> are you able to login and do work using QA !? what type of work you
> wants
> to accomplish
No, I'm not able to. I want to register EM, be able to login to QA, and run
Profiler. I don't have a sql login and am not in the domain - so I can't
use trusted logins.
Andre|||Hi,
then you must ensure that you have proper privillages ,
using sp_adduser to add your users to fixeddb role,
to ensure the associated role for yr user get script from :
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10504
HTH
Regards

access sql server in different domain

I just started as a consultant with a company who won't allow my notebook to
join the domain. No biggee - I can understand the security concerns. I am
able to get on the network and can Remote Desktop to the SQL Servers I need
to get to. However, life would be a heck of a lot better if I could map the
servers in EM. The problem is that the only user id I have in SQL is my
Windows id, not a local SQL Server id. And since I'm logged into my
notebook using cached credentials from a different domain, I can't register
the server in EM. I've read an article about using "net use \\<server>\ipc$
/user:<serverdomain>\<userid> <password>" and I was able to successfully run
this in a command window. I just don't really understand what it did for
me - because I still can't connect to the server in EM.
Is it possible to do what I want to do? If a solution is available in
Yukon, that would be acceptable too.
Thanks, AndreAndre,
you might try to start EM with the "run as" option, but I doubt it will
work. As an alternative you could try using the WebAdmin utility. It's
more limited than EM, but for most tasks it would be sufficient.
You can download it at
http://www.microsoft.com/downloads/...&displaylang=en
After install make sure you copy the updated
WebUIValidation.js file from
WINDOWS\Microsoft.NET\Framework\v1.1.4322
\ASP.NETClientFiles into {SQL Web Administrator
installation path}\Web\aspnet_client\system_web\1_1_4
322\
otherwise the programm hangs when you click the 'login' button.
Hope this helps
M|||Sorry, me again, must be the lack of coffee, but why don't you just
register the server using your SQL login ? Even though the server is in
a different domain this should work as long as your machine can resolve
the name.
M|||Hi,
as suggested try using sql login, or you could add / map yr windows login to
sql fixed role so that it will works for you.
regards|||I don't have a SQL login - only a Windows login that has been granted access
to SQL.
Andre
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1129188096.681173.270080@.g49g2000cwa.googlegroups.com...
> Sorry, me again, must be the lack of coffee, but why don't you just
> register the server using your SQL login ? Even though the server is in
> a different domain this should work as long as your machine can resolve
> the name.
> M
>|||I tried to use the WebAdmin utility but I'm not getting it to work. Even
though I copied the file you mentioned, I'm never able to connect to sql -
even my local installation.
And even if I was able to get that to work, it still looks like the only
ways of connecting is using trusted or sql credentials. Unless I can trick
it by selecting sql credentials then typing in domain\username, I'm not sure
this would help me login to a sql server in another domain.
Any tips you have are appreciated.
Thanks, Andre|||Hi,
are you able to login and do work using QA !? what type of work you wants
to accomplish
Regards|||> are you able to login and do work using QA !? what type of work you
> wants
> to accomplish
No, I'm not able to. I want to register EM, be able to login to QA, and run
Profiler. I don't have a sql login and am not in the domain - so I can't
use trusted logins.
Andre|||Hi,
then you must ensure that you have proper privillages ,
using sp_adduser to add your users to fixeddb role,
to ensure the associated role for yr user get script from :
http://www.sql-server-performance.c...?TOPIC_ID=10504
HTH
Regards

Access permission to run SQL debugger

Hi All,
When I try to step into the stored procedure (for debugging) in from vb.net
environment, it gives the follwoing error.
"Security hasn't been set up correctly for SQL debugging on server
<servername>. SQL Debugging terminated. See SQL Debugginh documentation on
how to set it up correctly."
And
"User does not have permission to execute master.sp_sdidebug"
I was able to debug the stored yeasterday. After that, I had installed
windows automatic updates. I observed that I started geting the error after
the update.
Any help is greatly appreciated.
Thanks.
kdSee if this helps:
http://msdn.microsoft.com/library/d...>
ols_5cfm.asp
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:E35AD11F-F140-4779-B8C8-CCB2D06F785A@.microsoft.com...
> Hi All,
> When I try to step into the stored procedure (for debugging) in from
vb.net
> environment, it gives the follwoing error.
> "Security hasn't been set up correctly for SQL debugging on server
> <servername>. SQL Debugging terminated. See SQL Debugginh documentation on
> how to set it up correctly."
> And
> "User does not have permission to execute master.sp_sdidebug"
> I was able to debug the stored yeasterday. After that, I had installed
> windows automatic updates. I observed that I started geting the error
after
> the update.
> Any help is greatly appreciated.
> Thanks.
> kd

Access Issue in in 2005

I have some software that connects to SQL Server using Integrated Security in my connection string. My users are members of an active directory group. In SQL Server 2005, I've added the AD group to Logins, checked the User Mapping for the database, and checked role memberships db_datareader, db_datawriter and public.

When I look at Properties->Permissions of the database, the AD group is listed under Users or roles. I checked Connect, Execute, Select, Insert and Update are checked.

The users in the AD group still cannot access the data. 2 questions:

-Is there a log that will tell me why they're getting blocked?

-Is there a better way to give access to a new group of people?

Thanks!

Brian

I think you're going down the right route adding the user to pre defined roles. However, can you clarify whether users of the group are able to login to the SQL Server or do they just have permission denied on the objects within the specific database? What message do you receive back when one of these users attempts to login and read the data?

|||

>> What message do you receive back when one of these users attempts to login and read the data?

I was attempting to answer this question (by adding better exception handling) when things started working. The last thing I did in SQL Server was checked the SELECT and INSERT boxes under the role. I didn't think it fixed the problem because the app still crashed, but there must have been a problem in the code that I fixed too.

Thanks!

Brian

Access Issue after Automatic Update

I finally added WinXP SP2 to my system yesterday (along with a lot of other security updates/patches through Automatic Updates) and now my backups and data exports are failing. Further, when I open my "Enterprise Manager" and try to export data ("All Tasks-Export-...") using the Import/Export Wizard, it won't let me select the "Export From" database.Can you give error message here or something?

Access deny SA Account

For security reason, does it possible to deny the account sa to access the
SQL Server 2000. I have tried using sp_denylogin or sp_revokelogin but both
failure.
Thanks in advance for any suggestion
Nope. Set a string password and make sure that no.one knows that password. Or run SQL Server in
Windows Only mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Tsang" <thomast@.hkc.net> wrote in message news:%23HFvs7MwEHA.3872@.TK2MSFTNGP11.phx.gbl...
> For security reason, does it possible to deny the account sa to access the
> SQL Server 2000. I have tried using sp_denylogin or sp_revokelogin but both
> failure.
> Thanks in advance for any suggestion
>
|||Even if you run SQL Server in Windows Authentication Only mode, you still
want to set a highly complex password for the sa account. The security mode
setting is only a Windows Registry key setting and is easily switched to
Mixed Authentication mode.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:

> Nope. Set a string password and make sure that no.one knows that password. Or run SQL Server in
> Windows Only mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Thomas Tsang" <thomast@.hkc.net> wrote in message news:%23HFvs7MwEHA.3872@.TK2MSFTNGP11.phx.gbl...
>
>
|||I absolutely agree (this is one thing I always push in classes). Thanks for bringing that up. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:FA9CF586-CC7C-4D84-B95A-46838D49B32A@.microsoft.com...[vbcol=seagreen]
> Even if you run SQL Server in Windows Authentication Only mode, you still
> want to set a highly complex password for the sa account. The security mode
> setting is only a Windows Registry key setting and is easily switched to
> Mixed Authentication mode.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
news:%23HFvs7MwEHA.3872@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]

Access deny SA Account

For security reason, does it possible to deny the account sa to access the
SQL Server 2000. I have tried using sp_denylogin or sp_revokelogin but both
failure.
Thanks in advance for any suggestionNope. Set a string password and make sure that no.one knows that password. Or run SQL Server in
Windows Only mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Tsang" <thomast@.hkc.net> wrote in message news:%23HFvs7MwEHA.3872@.TK2MSFTNGP11.phx.gbl...
> For security reason, does it possible to deny the account sa to access the
> SQL Server 2000. I have tried using sp_denylogin or sp_revokelogin but both
> failure.
> Thanks in advance for any suggestion
>|||Even if you run SQL Server in Windows Authentication Only mode, you still
want to set a highly complex password for the sa account. The security mode
setting is only a Windows Registry key setting and is easily switched to
Mixed Authentication mode.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Nope. Set a string password and make sure that no.one knows that password. Or run SQL Server in
> Windows Only mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Thomas Tsang" <thomast@.hkc.net> wrote in message news:%23HFvs7MwEHA.3872@.TK2MSFTNGP11.phx.gbl...
> > For security reason, does it possible to deny the account sa to access the
> > SQL Server 2000. I have tried using sp_denylogin or sp_revokelogin but both
> > failure.
> >
> > Thanks in advance for any suggestion
> >
>
>|||I absolutely agree (this is one thing I always push in classes). Thanks for bringing that up. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:FA9CF586-CC7C-4D84-B95A-46838D49B32A@.microsoft.com...
> Even if you run SQL Server in Windows Authentication Only mode, you still
> want to set a highly complex password for the sa account. The security mode
> setting is only a Windows Registry key setting and is easily switched to
> Mixed Authentication mode.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
> > Nope. Set a string password and make sure that no.one knows that password. Or run SQL Server in
> > Windows Only mode.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Thomas Tsang" <thomast@.hkc.net> wrote in message
news:%23HFvs7MwEHA.3872@.TK2MSFTNGP11.phx.gbl...
> > > For security reason, does it possible to deny the account sa to access the
> > > SQL Server 2000. I have tried using sp_denylogin or sp_revokelogin but both
> > > failure.
> > >
> > > Thanks in advance for any suggestion
> > >
> >
> >
> >

Access deny SA Account

For security reason, does it possible to deny the account sa to access the
SQL Server 2000. I have tried using sp_denylogin or sp_revokelogin but both
failure.
Thanks in advance for any suggestionNope. Set a string password and make sure that no.one knows that password. O
r run SQL Server in
Windows Only mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Tsang" <thomast@.hkc.net> wrote in message news:%23HFvs7MwEHA.3872@.TK2MSFTNGP11.phx.g
bl...
> For security reason, does it possible to deny the account sa to access the
> SQL Server 2000. I have tried using sp_denylogin or sp_revokelogin but bo
th
> failure.
> Thanks in advance for any suggestion
>|||Even if you run SQL Server in Windows Authentication Only mode, you still
want to set a highly complex password for the sa account. The security mode
setting is only a Windows Registry key setting and is easily switched to
Mixed Authentication mode.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:

> Nope. Set a string password and make sure that no.one knows that password.
Or run SQL Server in
> Windows Only mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Thomas Tsang" <thomast@.hkc.net> wrote in message news:%23HFvs7MwEHA.3872@.
TK2MSFTNGP11.phx.gbl...
>
>|||I absolutely agree (this is one thing I always push in classes). Thanks for
bringing that up. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:FA9CF586-CC7C-4D84-B95A-46838D49B32A@.microsoft.com...[vbcol=seagreen]
> Even if you run SQL Server in Windows Authentication Only mode, you still
> want to set a highly complex password for the sa account. The security mo
de
> setting is only a Windows Registry key setting and is easily switched to
> Mixed Authentication mode.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
>
news:%23HFvs7MwEHA.3872@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]

Access Denied in .NET web application after applying Security Patc

I've been asked to fix a problem with a web application residing on a
client's Windows 2003 server that is now denying access to the SQL database
after windows security patches were applied to the server.
The patches that were applied are:
Security Update for Windows Server 2003 (KB885836)
Security Update for Windows Server 2003 (KB870763)
Security Update for Windows Server 2003 (KB885835)
Security Update for Windows Server 2003 (KB873339)
And the exception message I'm receiving is:
System.UnauthorizedAccessException: Access is denied.
at System.EnterpriseServices.Platform.Initialize()
at System.EnterpriseServices.ResourcePool..ctor(TransactionEndDelegate cb)
at System.Data.SqlClient.ConnectionPool..ctor(DefaultPoolControl ctrl)
at System.Data.SqlClient.PoolManager.FindOrCreatePool(DefaultPoolControl
ctrl, IntPtr SID) at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at RollingThunder.RTOQuery.OpenConnection()
at MatterMgmt.History.StatusByDate.ShowSelectedDayPurchaseOrders()
in c:\inetpub\wwwroot\mattermgmt\history\st
atusbydate.aspx.cs:line 72
The exception is being thrown when trying to open the connection to SQL
(RtoQuery.OpenConnection();) when making a simple Select query.
I've been at this since yesterday now and any help would be really
appreciated.
Thanks in advance!
~ LouisHi Lou,
Try creating a test ODBC DSN and see if you get an error message. Reply
back with the entire message.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.