Re: Linked Server Confusion - I don't know where to start...

Hi Sue!

Thanks so much for trying to help me! I'm really stuck on this one.
OK, let me try and answer your questions:

1) Is the job owner a member of the sysadmins server role?

ANSWER: Yes, in fact, the job owner REPL is a member of every server
role except SecurityAdmin

2) Did you check the service account to make sure there aren't any
problems with the account, can you log on to the server using the
service account?

It looks like there are 6 services related to SQL Server:

a) SQL Server -
.\administrator
b) SQL Server Active Directory Helper -
NT AUTHORITY\NetworkService
c) SQL Server Agent -
HT01\Administrator
d) SQL Server Browser -
HT01\Administrator
e) SQL Server FullText Search -
.\Administrator
f) SQL Server VSS Writer -
Local System Account

I'm guessing that the service you're concerned about is the SQL Server
Agent. HT01 is our domain, so that is the domain administrator
account. I can certainly vouch for this account being a good one. It
does work. And the service does start. Is there a problem with the
SQL Server (a) being on a differant account than the agent (c)?

Thanks in advance for any help or guesses you can give!

-Rick

Sue Hoegemeier wrote:
Is the job owner a member of the sysadmins server role?
Did you check the service account to make sure there aren't
any problems with the account, can you log on to the server
using the service account?

-Sue

On 12 Apr 2006 09:37:16 -0700, "Datamover"
<datamovr@xxxxxxxxx> wrote:

Hi all,

I'll provide as much detail as I can. I have read many messages
regarding this topic, but I just can't seem to follow them in my
specific case. Sorry I'm so dumb...


Here goes:


1) I have a Microsoft SQL Server 2005 installed in mixed mode (NT
security plus standard SQL security). I don't know much about NT
security as I've never used it in connection with SQL Server.


2) I have set up a linked server called Max which uses the provider
OLEDB for ODBC.
3) I have a system DSN set up on the SQL Server to point to my ODBC
data source (A Maximizer CRM ODBC Driver, but I don't think this is
important)
4) On the security tab of the linked server, I have selected: For a
login not defined in the list above, connections will be made using
this security context. Below that, I specified the user ID and
password. The top box (server login mappings) is empty. Basically, I
want any user who runs a query to the linked server to use the user ID
and password I specified in the box below. No restrictions (at least
until I understand the security model a lot better than I do now)


So far, so good. I log in using a query tool using the standard SQL
Security ID I want to be using for this task in production later and
execute my stored procedure. Everything works. My stored procedure
connects to my remote (linked) server and does exactly what it should
do. No security issues at all...


So, I set up a "job" to run that stored procedure at certain times of
the day. That job fails every time. The error is:


"Executed as user: REPL Access to the remote server is denied because
the current securty context is not trusted.[SQLSTATE 42000] ERROR:
15274 The step failed."


By the way, REPL is the standard SQL Server security user ID I want
this job to run as. I know I must be very close to getting this to
work, but as I read the articles from Microsoft, my eyes just glaze
over and I start looking for coffee...


In your reply, please be very specific. If I need to do something to
my NT Security or something like that, please try to provide the
steps...


Also, I should tell you that I do not have a Windows 2000 or Windows
Server 2003 domain. I do have a PDC (Primary domain controller)
running on a Windows NT 4.0 Server. (On a side note, I would be happy
to upgrade my PDC to my Windows 2003 R2 server, but I don't know how to

do that either)


Anyone want to help out a dummy?


Thanks in advance!


-Rick
datamovr at yahoo dot com

.

Relevant Pages

  • Re: SMS_MP_Control_Manager Errors
    ... A colleage of mine figure it out, it was "local security policy" problem, he ... IUSR_"Computer account" must be able to access the computer from the network. ... delete the Guests group from it. ... Verify that the SQL server is properly configured to ...
    (microsoft.public.sms.admin)
  • Http verification .sms_aut (port 80) failed
    ... I noticed I couldn't get SMS reports to work.... ... MP encountered an error when connecting to SQL Server. ... If using a standard SQL security account, ...
    (microsoft.public.sms.admin)
  • Re: Http verification .sms_aut (port 80) failed
    ... I resolved a problem like this changing the Security settings on SMS_MP ... I had to go into SQL Server Configuration Manager / SQL Server 2005 Network ... I noticed I couldn't get SMS reports to work.... ... If using a standard SQL security account, ...
    (microsoft.public.sms.admin)
  • Re: 2005 Enterprise sp 2 install fails - 11009
    ... Q...Was your SQL Server service account a domain administrator account? ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL windows account
    ... > Can the account to run SQL server belong to USER group or ... >>SQL Service account absoloutely does NOT have ... >>Jasper Smith (SQL Server MVP) ... >>> I happened to be a box and DB admin and i have a local ...
    (microsoft.public.sqlserver.security)