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

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 comIs 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@.yahoo.com> 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|||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:[vbcol=seagreen]
> 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@.yahoo.com> wrote:
>|||One thing I forgot to mention, the job fails at exactly the point in
the stored procedure where it tries to insert a row into a linked
server table. This is the only line in the code that connects to the
linked server. The error I'm getting is certainly related to the
linked server.|||If you can execute the stored procedure manually but it
fails when run as a job, it's often an issue in the area of
security context differences. The job is run under the
security context of the Agent service, manually it's running
under the context of your login. You may also want to check
the properties of the DSN. In the ODBC Data Source
Administrator, select the DSN and the click configure. Click
the client configuration button on the second screen. Make
sure the protocol is set to TCP/IP. In the Linked Server
security settings, I'd also try explicitly mapping the Agent
service account to the login you want to use.
-Sue
On 13 Apr 2006 08:23:39 -0700, "Datamover"
<datamovr@.yahoo.com> wrote:

>One thing I forgot to mention, the job fails at exactly the point in
>the stored procedure where it tries to insert a row into a linked
>server table. This is the only line in the code that connects to the
>linked server. The error I'm getting is certainly related to the
>linked server.|||Hi Sue,
Thank you very much for your continued help!
Please help me to understand your post.
First, the ODBC driver I'm using is not SQL Server, and there is no
protocol choice. This is a connection to a proprietary database.
There is no way to choose TCP/IP.
Second, the SQL Agent is logging in as the domain administrator
account. I don't know which account I could have it use that would
have more rights than that. I don't understand what you want me to
change it to? The job needs to run as REPL, but that is a SQL Server
account. It is not an NT account. Is there a way to make it both?
I'm open to just about anything at this point!
-Rick|||On the ODBC driver, if you have no choice you have no
choice.
You can't have an account be a SQL login and a Windows
login.
I don't know enough about this other proprietary database,
where it is, how you are connecting to it (DSN doesn't tell
help much). Again, if you can execute this manually but it
doesn't work when scheduled as a job, it's generally related
to issues on differences in security contexts. The following
article is related and you'd still use some of the same
troubleshooting steps:
http://support.microsoft.com/?id=269074
-Sue
On 13 Apr 2006 20:53:50 -0700, "Datamover"
<datamovr@.yahoo.com> wrote:

>Hi Sue,
>Thank you very much for your continued help!
>Please help me to understand your post.
>First, the ODBC driver I'm using is not SQL Server, and there is no
>protocol choice. This is a connection to a proprietary database.
>There is no way to choose TCP/IP.
>Second, the SQL Agent is logging in as the domain administrator
>account. I don't know which account I could have it use that would
>have more rights than that. I don't understand what you want me to
>change it to? The job needs to run as REPL, but that is a SQL Server
>account. It is not an NT account. Is there a way to make it both?
>I'm open to just about anything at this point!
>-Rick|||I encountered the same problem as yours, i did the following to rectify
the issue. Hope this helps.
1) Added a new windows NT ID to the administrators group of the SQL
Server. (Both 2000 and 2005 servers)
2) Started the SQL Server Agent with that ID (Both SQL2000 and SQL2005).
3) Gave full access to the SQL DB (on both the servers) for the same
WindowsNT login
4) In the Linked Server security tab, i gave the NT Account and made it
to impersonate. (Did this on both SQL2000 and SQL2005)
5) Ran the statement EXEC sp_addlinkedsrvlogin '<SQLINSTANCE NAME>',
'true' (on each of the servers)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***