Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
Regards,
Jamie
Forgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Showing posts with label login. Show all posts
Showing posts with label login. Show all posts
Monday, March 26, 2012
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
--
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
--
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission t
o
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to acces
s
table3 on DB2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allow
ed
> us to have both the ability to login under a given security context as wel
l
> as specify individual logins. The linked server also contains the provisi
on
> for impersonation in 2005. If possible, I would like to allow permission
to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do no
t
> want. Instead, can I make the access work such that if UserA tries to acc
ess
> table3 on DB2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list und
er
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission t
o
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to acces
s
table3 on DB2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allow
ed
> us to have both the ability to login under a given security context as wel
l
> as specify individual logins. The linked server also contains the provisi
on
> for impersonation in 2005. If possible, I would like to allow permission
to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do no
t
> want. Instead, can I make the access work such that if UserA tries to acc
ess
> table3 on DB2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list und
er
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Friday, March 23, 2012
linked server Login timeout expired error
Hello All,
My application is dependent on two databases one is on sql 2005 machine and other is on sql 2000 machine. A linked server is establish between these two using sql account which has appropriate rights on both server's databases.
application works fine but after some time it crashes and if i see error from sql profiler i get the error
OLE DB provider "SQLNCLI" for linked server "sql2000machine" returned message "Login timeout expired".
Few days back applciation was working absolutly fine, but i was trying to make linked sever through windows account, i dnt know wat has i changed during achiveing that setup that now application (linked server) is not working even with sql account.
please help its urgent.
Check network setting. Can you ping from sql2k5 to sql2k. Also, how is the linked server login setup? Is it "self" or the remote credential? Remember that domain authentication requires a domain controller. Network latency might be your enemy here.
sqllinked server login problem... with a particular login... on a particular box
Im having a problem with one particular Linked Server Login. I've created
two of them. Neither of which has an existing Login on the Linked Server and
both of which are mapped to a member of SA. In the below examply, Login1
will work, but Login2 will fail with:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login1'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login2'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
Again, please keep in mind that the Login on the Linked Server is in the SA
role, and that the top Login works. Another important tip, both of these
work successfully on another box, just not the one that counts.
All ideas are greatly appreciated as Im quite desperate.
--
SQL2K SP3
TIA, ChrisRI think its related to this issue but Im not sure.
http://support.microsoft.com/default.aspx?scid=kb;en-us;238477
I don't really need a fix for this anyomore as I've found a workaround and
only needed this temporarily. More info: I had opened Query Analyzer under
the failing account on my local box... RClick/ Run as. But if I log directly
onto the box I was trying to run the query from it works fine. Which is why
Im thinking its related to the above article.
"ChrisR" <bla@.noemail.com> wrote in message
news:#uUSqB4EFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Im having a problem with one particular Linked Server Login. I've created
> two of them. Neither of which has an existing Login on the Linked Server
and
> both of which are mapped to a member of SA. In the below examply, Login1
> will work, but Login2 will fail with:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login1'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login2'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> Again, please keep in mind that the Login on the Linked Server is in the
SA
> role, and that the top Login works. Another important tip, both of these
> work successfully on another box, just not the one that counts.
> All ideas are greatly appreciated as Im quite desperate.
> --
> SQL2K SP3
> TIA, ChrisR
>
two of them. Neither of which has an existing Login on the Linked Server and
both of which are mapped to a member of SA. In the below examply, Login1
will work, but Login2 will fail with:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login1'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login2'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
Again, please keep in mind that the Login on the Linked Server is in the SA
role, and that the top Login works. Another important tip, both of these
work successfully on another box, just not the one that counts.
All ideas are greatly appreciated as Im quite desperate.
--
SQL2K SP3
TIA, ChrisRI think its related to this issue but Im not sure.
http://support.microsoft.com/default.aspx?scid=kb;en-us;238477
I don't really need a fix for this anyomore as I've found a workaround and
only needed this temporarily. More info: I had opened Query Analyzer under
the failing account on my local box... RClick/ Run as. But if I log directly
onto the box I was trying to run the query from it works fine. Which is why
Im thinking its related to the above article.
"ChrisR" <bla@.noemail.com> wrote in message
news:#uUSqB4EFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Im having a problem with one particular Linked Server Login. I've created
> two of them. Neither of which has an existing Login on the Linked Server
and
> both of which are mapped to a member of SA. In the below examply, Login1
> will work, but Login2 will fail with:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login1'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login2'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> Again, please keep in mind that the Login on the Linked Server is in the
SA
> role, and that the top Login works. Another important tip, both of these
> work successfully on another box, just not the one that counts.
> All ideas are greatly appreciated as Im quite desperate.
> --
> SQL2K SP3
> TIA, ChrisR
>
linked server login problem... with a particular login... on a particular box
Im having a problem with one particular Linked Server Login. I've created
two of them. Neither of which has an existing Login on the Linked Server and
both of which are mapped to a member of SA. In the below examply, Login1
will work, but Login2 will fail with:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login1'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login2'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
Again, please keep in mind that the Login on the Linked Server is in the SA
role, and that the top Login works. Another important tip, both of these
work successfully on another box, just not the one that counts.
All ideas are greatly appreciated as Im quite desperate.
SQL2K SP3
TIA, ChrisRI think its related to this issue but Im not sure.
http://support.microsoft.com/defaul...kb;en-us;238477
I don't really need a fix for this anyomore as I've found a workaround and
only needed this temporarily. More info: I had opened Query Analyzer under
the failing account on my local box... RClick/ Run as. But if I log directly
onto the box I was trying to run the query from it works fine. Which is why
Im thinking its related to the above article.
"ChrisR" <bla@.noemail.com> wrote in message
news:#uUSqB4EFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Im having a problem with one particular Linked Server Login. I've created
> two of them. Neither of which has an existing Login on the Linked Server
and
> both of which are mapped to a member of SA. In the below examply, Login1
> will work, but Login2 will fail with:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login1'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login2'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> Again, please keep in mind that the Login on the Linked Server is in the
SA
> role, and that the top Login works. Another important tip, both of these
> work successfully on another box, just not the one that counts.
> All ideas are greatly appreciated as Im quite desperate.
> --
> SQL2K SP3
> TIA, ChrisR
>
two of them. Neither of which has an existing Login on the Linked Server and
both of which are mapped to a member of SA. In the below examply, Login1
will work, but Login2 will fail with:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login1'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login2'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
Again, please keep in mind that the Login on the Linked Server is in the SA
role, and that the top Login works. Another important tip, both of these
work successfully on another box, just not the one that counts.
All ideas are greatly appreciated as Im quite desperate.
SQL2K SP3
TIA, ChrisRI think its related to this issue but Im not sure.
http://support.microsoft.com/defaul...kb;en-us;238477
I don't really need a fix for this anyomore as I've found a workaround and
only needed this temporarily. More info: I had opened Query Analyzer under
the failing account on my local box... RClick/ Run as. But if I log directly
onto the box I was trying to run the query from it works fine. Which is why
Im thinking its related to the above article.
"ChrisR" <bla@.noemail.com> wrote in message
news:#uUSqB4EFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Im having a problem with one particular Linked Server Login. I've created
> two of them. Neither of which has an existing Login on the Linked Server
and
> both of which are mapped to a member of SA. In the below examply, Login1
> will work, but Login2 will fail with:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login1'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login2'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> Again, please keep in mind that the Login on the Linked Server is in the
SA
> role, and that the top Login works. Another important tip, both of these
> work successfully on another box, just not the one that counts.
> All ideas are greatly appreciated as Im quite desperate.
> --
> SQL2K SP3
> TIA, ChrisR
>
linked server login problem... with a particular login... on a particular box
Im having a problem with one particular Linked Server Login. I've created
two of them. Neither of which has an existing Login on the Linked Server and
both of which are mapped to a member of SA. In the below examply, Login1
will work, but Login2 will fail with:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login1'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login2'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
Again, please keep in mind that the Login on the Linked Server is in the SA
role, and that the top Login works. Another important tip, both of these
work successfully on another box, just not the one that counts.
All ideas are greatly appreciated as Im quite desperate.
SQL2K SP3
TIA, ChrisR
I think its related to this issue but Im not sure.
http://support.microsoft.com/default...b;en-us;238477
I don't really need a fix for this anyomore as I've found a workaround and
only needed this temporarily. More info: I had opened Query Analyzer under
the failing account on my local box... RClick/ Run as. But if I log directly
onto the box I was trying to run the query from it works fine. Which is why
Im thinking its related to the above article.
"ChrisR" <bla@.noemail.com> wrote in message
news:#uUSqB4EFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Im having a problem with one particular Linked Server Login. I've created
> two of them. Neither of which has an existing Login on the Linked Server
and
> both of which are mapped to a member of SA. In the below examply, Login1
> will work, but Login2 will fail with:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login1'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login2'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> Again, please keep in mind that the Login on the Linked Server is in the
SA
> role, and that the top Login works. Another important tip, both of these
> work successfully on another box, just not the one that counts.
> All ideas are greatly appreciated as Im quite desperate.
> --
> SQL2K SP3
> TIA, ChrisR
>
two of them. Neither of which has an existing Login on the Linked Server and
both of which are mapped to a member of SA. In the below examply, Login1
will work, but Login2 will fail with:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login1'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login2'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
Again, please keep in mind that the Login on the Linked Server is in the SA
role, and that the top Login works. Another important tip, both of these
work successfully on another box, just not the one that counts.
All ideas are greatly appreciated as Im quite desperate.
SQL2K SP3
TIA, ChrisR
I think its related to this issue but Im not sure.
http://support.microsoft.com/default...b;en-us;238477
I don't really need a fix for this anyomore as I've found a workaround and
only needed this temporarily. More info: I had opened Query Analyzer under
the failing account on my local box... RClick/ Run as. But if I log directly
onto the box I was trying to run the query from it works fine. Which is why
Im thinking its related to the above article.
"ChrisR" <bla@.noemail.com> wrote in message
news:#uUSqB4EFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Im having a problem with one particular Linked Server Login. I've created
> two of them. Neither of which has an existing Login on the Linked Server
and
> both of which are mapped to a member of SA. In the below examply, Login1
> will work, but Login2 will fail with:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login1'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login2'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> Again, please keep in mind that the Login on the Linked Server is in the
SA
> role, and that the top Login works. Another important tip, both of these
> work successfully on another box, just not the one that counts.
> All ideas are greatly appreciated as Im quite desperate.
> --
> SQL2K SP3
> TIA, ChrisR
>
Wednesday, March 21, 2012
Linked Server in SQL(2005) Job
I have a linked server to Sybase using MSDASQL. I can query the tables from the linked server using SQL Management Studio (windows login). When I put the same query into a job, it failed to work. The query is simple:
SELECT * from MyLinkedSvr.RemotDB.dbo.RemoteTable
The linked server setting include the login mapping, which maps my windows login and sa to the remote sybase login.
I'm using SQL 2005 9.0.1339 on Windows2000 Pro w/ SP4. SQL Agent starts as local system in the Services panel. Shared Memory/TCPIP/Named Pipes are enabled for Server/Client Network.
Thanks!
The problem is solved by switching the log on account of SQL Agent to a windows log in account. Seems SQL Jobs is running under the account that start SQL Agent service.
|||No exactly sure where to switch the logon account of SQL Agent. Help help?Linked Server in SQL(2005) Job
I have a linked server to Sybase using MSDASQL. I can query the tables from the linked server using SQL Management Studio (windows login). When I put the same query into a job, it failed to work. The query is simple:
SELECT * from MyLinkedSvr.RemotDB.dbo.RemoteTable
The linked server setting include the login mapping, which maps my windows login and sa to the remote sybase login.
I'm using SQL 2005 9.0.1339 on Windows2000 Pro w/ SP4. SQL Agent starts as local system in the Services panel. Shared Memory/TCPIP/Named Pipes are enabled for Server/Client Network.
Thanks!
The problem is solved by switching the log on account of SQL Agent to a windows log in account. Seems SQL Jobs is running under the account that start SQL Agent service.
|||No exactly sure where to switch the logon account of SQL Agent. Help help?Monday, March 19, 2012
Linked Server error in Windows2003Server
Dear all,
I've got this error message on creating the linked server to the SQL Server
2000 (in Windows 2003 Server),
"Error 18452: Login failed for user 'sa'. Reason: Not associated with a
trusted SQL Server connection."
The client side is MSDE 2000 (in WinXP Pro).
But I can successfully create this linked server relationship when the
server side is SQL Server 2000 (in Windows 2000 Server or WinXP Pro). If the
OS in server side is changed to Windows 2003 Server, the above error
occurred. Why?
Thanks a lot.
Curtis
Curtis,
I very much doubt that the OS version has anything to do with your
problem. The error message clearly shows that the Server you are trying
to connect to expects a trusted Windows login. The sa account is a SQL
Server login and that's why the login attempt fails. Change the
authentification mode on the SQL server to mixed (SQL and Windows
authentification) and it should work.
Markus
|||Thanks for you reply.
But for the Windows2003Server, the authentification mode is already mixed
mode.
One more thing, this error message has not been shown if both are the SQL
Server 2000 (in Windows2003Server). It's only appeared with either one is
the WinXP Pro. So, I've suspected that anything goes wrong in the client.
P.S.: Also, I've just walked through different forums, and the solution is
same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
problem.
Curtis
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1141135359.765464.289340@.t39g2000cwt.googlegr oups.com...
> Curtis,
> I very much doubt that the OS version has anything to do with your
> problem. The error message clearly shows that the Server you are trying
> to connect to expects a trusted Windows login. The sa account is a SQL
> Server login and that's why the login attempt fails. Change the
> authentification mode on the SQL server to mixed (SQL and Windows
> authentification) and it should work.
> Markus
>
|||Lun wrote:
> Thanks for you reply.
> But for the Windows2003Server, the authentification mode is already mixed
> mode.
> One more thing, this error message has not been shown if both are the SQL
> Server 2000 (in Windows2003Server). It's only appeared with either one is
> the WinXP Pro. So, I've suspected that anything goes wrong in the client.
> P.S.: Also, I've just walked through different forums, and the solution is
> same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
> problem.
> Curtis
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1141135359.765464.289340@.t39g2000cwt.googlegr oups.com...
>
Have you created a linkedserver login? It sounds like you're trying to
connect with a SQL user but that you haven't linked this user to a login
on the linked server.
Try to look up sp_addlinkedserver and/or sp_addlinkedsrvlogin in Books
On Line.
Regards
Steen
I've got this error message on creating the linked server to the SQL Server
2000 (in Windows 2003 Server),
"Error 18452: Login failed for user 'sa'. Reason: Not associated with a
trusted SQL Server connection."
The client side is MSDE 2000 (in WinXP Pro).
But I can successfully create this linked server relationship when the
server side is SQL Server 2000 (in Windows 2000 Server or WinXP Pro). If the
OS in server side is changed to Windows 2003 Server, the above error
occurred. Why?
Thanks a lot.
Curtis
Curtis,
I very much doubt that the OS version has anything to do with your
problem. The error message clearly shows that the Server you are trying
to connect to expects a trusted Windows login. The sa account is a SQL
Server login and that's why the login attempt fails. Change the
authentification mode on the SQL server to mixed (SQL and Windows
authentification) and it should work.
Markus
|||Thanks for you reply.
But for the Windows2003Server, the authentification mode is already mixed
mode.
One more thing, this error message has not been shown if both are the SQL
Server 2000 (in Windows2003Server). It's only appeared with either one is
the WinXP Pro. So, I've suspected that anything goes wrong in the client.
P.S.: Also, I've just walked through different forums, and the solution is
same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
problem.
Curtis
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1141135359.765464.289340@.t39g2000cwt.googlegr oups.com...
> Curtis,
> I very much doubt that the OS version has anything to do with your
> problem. The error message clearly shows that the Server you are trying
> to connect to expects a trusted Windows login. The sa account is a SQL
> Server login and that's why the login attempt fails. Change the
> authentification mode on the SQL server to mixed (SQL and Windows
> authentification) and it should work.
> Markus
>
|||Lun wrote:
> Thanks for you reply.
> But for the Windows2003Server, the authentification mode is already mixed
> mode.
> One more thing, this error message has not been shown if both are the SQL
> Server 2000 (in Windows2003Server). It's only appeared with either one is
> the WinXP Pro. So, I've suspected that anything goes wrong in the client.
> P.S.: Also, I've just walked through different forums, and the solution is
> same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
> problem.
> Curtis
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1141135359.765464.289340@.t39g2000cwt.googlegr oups.com...
>
Have you created a linkedserver login? It sounds like you're trying to
connect with a SQL user but that you haven't linked this user to a login
on the linked server.
Try to look up sp_addlinkedserver and/or sp_addlinkedsrvlogin in Books
On Line.
Regards
Steen
Linked Server error in Windows2003Server
Dear all,
I've got this error message on creating the linked server to the SQL Server
2000 (in Windows 2003 Server),
"Error 18452: Login failed for user 'sa'. Reason: Not associated with a
trusted SQL Server connection."
The client side is MSDE 2000 (in WinXP Pro).
But I can successfully create this linked server relationship when the
server side is SQL Server 2000 (in Windows 2000 Server or WinXP Pro). If the
OS in server side is changed to Windows 2003 Server, the above error
occurred. Why?
Thanks a lot.
CurtisCurtis,
I very much doubt that the OS version has anything to do with your
problem. The error message clearly shows that the Server you are trying
to connect to expects a trusted Windows login. The sa account is a SQL
Server login and that's why the login attempt fails. Change the
authentification mode on the SQL server to mixed (SQL and Windows
authentification) and it should work.
Markus|||Thanks for you reply.
But for the Windows2003Server, the authentification mode is already mixed
mode.
One more thing, this error message has not been shown if both are the SQL
Server 2000 (in Windows2003Server). It's only appeared with either one is
the WinXP Pro. So, I've suspected that anything goes wrong in the client.
P.S.: Also, I've just walked through different forums, and the solution is
same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
problem.
Curtis
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
> Curtis,
> I very much doubt that the OS version has anything to do with your
> problem. The error message clearly shows that the Server you are trying
> to connect to expects a trusted Windows login. The sa account is a SQL
> Server login and that's why the login attempt fails. Change the
> authentification mode on the SQL server to mixed (SQL and Windows
> authentification) and it should work.
> Markus
>|||Lun wrote:
> Thanks for you reply.
> But for the Windows2003Server, the authentification mode is already mixed
> mode.
> One more thing, this error message has not been shown if both are the SQL
> Server 2000 (in Windows2003Server). It's only appeared with either one is
> the WinXP Pro. So, I've suspected that anything goes wrong in the client.
> P.S.: Also, I've just walked through different forums, and the solution is
> same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
> problem.
> Curtis
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
>> Curtis,
>> I very much doubt that the OS version has anything to do with your
>> problem. The error message clearly shows that the Server you are trying
>> to connect to expects a trusted Windows login. The sa account is a SQL
>> Server login and that's why the login attempt fails. Change the
>> authentification mode on the SQL server to mixed (SQL and Windows
>> authentification) and it should work.
>> Markus
>
Have you created a linkedserver login? It sounds like you're trying to
connect with a SQL user but that you haven't linked this user to a login
on the linked server.
Try to look up sp_addlinkedserver and/or sp_addlinkedsrvlogin in Books
On Line.
Regards
Steen
I've got this error message on creating the linked server to the SQL Server
2000 (in Windows 2003 Server),
"Error 18452: Login failed for user 'sa'. Reason: Not associated with a
trusted SQL Server connection."
The client side is MSDE 2000 (in WinXP Pro).
But I can successfully create this linked server relationship when the
server side is SQL Server 2000 (in Windows 2000 Server or WinXP Pro). If the
OS in server side is changed to Windows 2003 Server, the above error
occurred. Why?
Thanks a lot.
CurtisCurtis,
I very much doubt that the OS version has anything to do with your
problem. The error message clearly shows that the Server you are trying
to connect to expects a trusted Windows login. The sa account is a SQL
Server login and that's why the login attempt fails. Change the
authentification mode on the SQL server to mixed (SQL and Windows
authentification) and it should work.
Markus|||Thanks for you reply.
But for the Windows2003Server, the authentification mode is already mixed
mode.
One more thing, this error message has not been shown if both are the SQL
Server 2000 (in Windows2003Server). It's only appeared with either one is
the WinXP Pro. So, I've suspected that anything goes wrong in the client.
P.S.: Also, I've just walked through different forums, and the solution is
same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
problem.
Curtis
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
> Curtis,
> I very much doubt that the OS version has anything to do with your
> problem. The error message clearly shows that the Server you are trying
> to connect to expects a trusted Windows login. The sa account is a SQL
> Server login and that's why the login attempt fails. Change the
> authentification mode on the SQL server to mixed (SQL and Windows
> authentification) and it should work.
> Markus
>|||Lun wrote:
> Thanks for you reply.
> But for the Windows2003Server, the authentification mode is already mixed
> mode.
> One more thing, this error message has not been shown if both are the SQL
> Server 2000 (in Windows2003Server). It's only appeared with either one is
> the WinXP Pro. So, I've suspected that anything goes wrong in the client.
> P.S.: Also, I've just walked through different forums, and the solution is
> same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
> problem.
> Curtis
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
>> Curtis,
>> I very much doubt that the OS version has anything to do with your
>> problem. The error message clearly shows that the Server you are trying
>> to connect to expects a trusted Windows login. The sa account is a SQL
>> Server login and that's why the login attempt fails. Change the
>> authentification mode on the SQL server to mixed (SQL and Windows
>> authentification) and it should work.
>> Markus
>
Have you created a linkedserver login? It sounds like you're trying to
connect with a SQL user but that you haven't linked this user to a login
on the linked server.
Try to look up sp_addlinkedserver and/or sp_addlinkedsrvlogin in Books
On Line.
Regards
Steen
Linked Server error in Windows2003Server
Dear all,
I've got this error message on creating the linked server to the SQL Server
2000 (in Windows 2003 Server),
"Error 18452: Login failed for user 'sa'. Reason: Not associated with a
trusted SQL Server connection."
The client side is MSDE 2000 (in WinXP Pro).
But I can successfully create this linked server relationship when the
server side is SQL Server 2000 (in Windows 2000 Server or WinXP Pro). If the
OS in server side is changed to Windows 2003 Server, the above error
occurred. Why?
Thanks a lot.
CurtisCurtis,
I very much doubt that the OS version has anything to do with your
problem. The error message clearly shows that the Server you are trying
to connect to expects a trusted Windows login. The sa account is a SQL
Server login and that's why the login attempt fails. Change the
authentification mode on the SQL server to mixed (SQL and Windows
authentification) and it should work.
Markus|||Thanks for you reply.
But for the Windows2003Server, the authentification mode is already mixed
mode.
One more thing, this error message has not been shown if both are the SQL
Server 2000 (in Windows2003Server). It's only appeared with either one is
the WinXP Pro. So, I've suspected that anything goes wrong in the client.
P.S.: Also, I've just walked through different forums, and the solution is
same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
problem.
Curtis
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
> Curtis,
> I very much doubt that the OS version has anything to do with your
> problem. The error message clearly shows that the Server you are trying
> to connect to expects a trusted Windows login. The sa account is a SQL
> Server login and that's why the login attempt fails. Change the
> authentification mode on the SQL server to mixed (SQL and Windows
> authentification) and it should work.
> Markus
>|||Lun wrote:
> Thanks for you reply.
> But for the Windows2003Server, the authentification mode is already mixed
> mode.
> One more thing, this error message has not been shown if both are the SQL
> Server 2000 (in Windows2003Server). It's only appeared with either one is
> the WinXP Pro. So, I've suspected that anything goes wrong in the client.
> P.S.: Also, I've just walked through different forums, and the solution is
> same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
> problem.
> Curtis
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
>
Have you created a linkedserver login? It sounds like you're trying to
connect with a SQL user but that you haven't linked this user to a login
on the linked server.
Try to look up sp_addlinkedserver and/or sp_addlinkedsrvlogin in Books
On Line.
Regards
Steen
I've got this error message on creating the linked server to the SQL Server
2000 (in Windows 2003 Server),
"Error 18452: Login failed for user 'sa'. Reason: Not associated with a
trusted SQL Server connection."
The client side is MSDE 2000 (in WinXP Pro).
But I can successfully create this linked server relationship when the
server side is SQL Server 2000 (in Windows 2000 Server or WinXP Pro). If the
OS in server side is changed to Windows 2003 Server, the above error
occurred. Why?
Thanks a lot.
CurtisCurtis,
I very much doubt that the OS version has anything to do with your
problem. The error message clearly shows that the Server you are trying
to connect to expects a trusted Windows login. The sa account is a SQL
Server login and that's why the login attempt fails. Change the
authentification mode on the SQL server to mixed (SQL and Windows
authentification) and it should work.
Markus|||Thanks for you reply.
But for the Windows2003Server, the authentification mode is already mixed
mode.
One more thing, this error message has not been shown if both are the SQL
Server 2000 (in Windows2003Server). It's only appeared with either one is
the WinXP Pro. So, I've suspected that anything goes wrong in the client.
P.S.: Also, I've just walked through different forums, and the solution is
same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
problem.
Curtis
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
> Curtis,
> I very much doubt that the OS version has anything to do with your
> problem. The error message clearly shows that the Server you are trying
> to connect to expects a trusted Windows login. The sa account is a SQL
> Server login and that's why the login attempt fails. Change the
> authentification mode on the SQL server to mixed (SQL and Windows
> authentification) and it should work.
> Markus
>|||Lun wrote:
> Thanks for you reply.
> But for the Windows2003Server, the authentification mode is already mixed
> mode.
> One more thing, this error message has not been shown if both are the SQL
> Server 2000 (in Windows2003Server). It's only appeared with either one is
> the WinXP Pro. So, I've suspected that anything goes wrong in the client.
> P.S.: Also, I've just walked through different forums, and the solution is
> same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
> problem.
> Curtis
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
>
Have you created a linkedserver login? It sounds like you're trying to
connect with a SQL user but that you haven't linked this user to a login
on the linked server.
Try to look up sp_addlinkedserver and/or sp_addlinkedsrvlogin in Books
On Line.
Regards
Steen
Linked Server Error
We had to reinstall MSSQL on one of our servers now when I set up a linked
server and try to associate a login I get the following error:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote
server 'TEST'.
Is there a fix for this it works from another server with the same edition
and configuration.I figured it out:
ALTER SERVICE MASTER KEY FORCE REGENERATE
"davids@.thorntonsinc.com" wrote:
> We had to reinstall MSSQL on one of our servers now when I set up a linked
> server and try to associate a login I get the following error:
> Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
> An error occurred during decryption.
> Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
> There is no remote user 'sa' mapped to local user '(null)' from the remote
> server 'TEST'.
> Is there a fix for this it works from another server with the same edition
> and configuration.
server and try to associate a login I get the following error:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote
server 'TEST'.
Is there a fix for this it works from another server with the same edition
and configuration.I figured it out:
ALTER SERVICE MASTER KEY FORCE REGENERATE
"davids@.thorntonsinc.com" wrote:
> We had to reinstall MSSQL on one of our servers now when I set up a linked
> server and try to associate a login I get the following error:
> Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
> An error occurred during decryption.
> Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
> There is no remote user 'sa' mapped to local user '(null)' from the remote
> server 'TEST'.
> Is there a fix for this it works from another server with the same edition
> and configuration.
Monday, March 12, 2012
Linked Server Error
We had to reinstall MSSQL on one of our servers now when I set up a linked
server and try to associate a login I get the following error:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote
server 'TEST'.
Is there a fix for this it works from another server with the same edition
and configuration.
I figured it out:
ALTER SERVICE MASTER KEY FORCE REGENERATE
"davids@.thorntonsinc.com" wrote:
> We had to reinstall MSSQL on one of our servers now when I set up a linked
> server and try to associate a login I get the following error:
> Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
> An error occurred during decryption.
> Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
> There is no remote user 'sa' mapped to local user '(null)' from the remote
> server 'TEST'.
> Is there a fix for this it works from another server with the same edition
> and configuration.
server and try to associate a login I get the following error:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote
server 'TEST'.
Is there a fix for this it works from another server with the same edition
and configuration.
I figured it out:
ALTER SERVICE MASTER KEY FORCE REGENERATE
"davids@.thorntonsinc.com" wrote:
> We had to reinstall MSSQL on one of our servers now when I set up a linked
> server and try to associate a login I get the following error:
> Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
> An error occurred during decryption.
> Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
> There is no remote user 'sa' mapped to local user '(null)' from the remote
> server 'TEST'.
> Is there a fix for this it works from another server with the same edition
> and configuration.
Linked Server Error
We had to reinstall MSSQL on one of our servers now when I set up a linked
server and try to associate a login I get the following error:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote
server 'TEST'.
Is there a fix for this it works from another server with the same edition
and configuration.I figured it out:
ALTER SERVICE MASTER KEY FORCE REGENERATE
"davids@.thorntonsinc.com" wrote:
> We had to reinstall MSSQL on one of our servers now when I set up a linked
> server and try to associate a login I get the following error:
> Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
> An error occurred during decryption.
> Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
> There is no remote user 'sa' mapped to local user '(null)' from the remote
> server 'TEST'.
> Is there a fix for this it works from another server with the same edition
> and configuration.
server and try to associate a login I get the following error:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote
server 'TEST'.
Is there a fix for this it works from another server with the same edition
and configuration.I figured it out:
ALTER SERVICE MASTER KEY FORCE REGENERATE
"davids@.thorntonsinc.com" wrote:
> We had to reinstall MSSQL on one of our servers now when I set up a linked
> server and try to associate a login I get the following error:
> Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
> An error occurred during decryption.
> Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
> There is no remote user 'sa' mapped to local user '(null)' from the remote
> server 'TEST'.
> Is there a fix for this it works from another server with the same edition
> and configuration.
Friday, February 24, 2012
Linked server - admin vs non-admin windows login
Any chance there was ever a resolution to this problem? We're having the
same issue and I cannot find the answer.
In our SQL 2000 server, it was a permissions problem to the sybase drivers
folder. On the server, we had to give "Authenticated Users" read access to
this folder. Once we did that, it worked. However, on our SQL 2005 server
we did this same thing and the initialization error went away but now our
queries just hang and never return. We have "Authenticated Users" in the
Users group on the server but if we add them to the Administrators group then
the queries run fine.
Any ideas would be greatly appreciated.
Thanks
Hi Doug
SQL 2005 has a tighter security model so making them a sysadmin would
probably bypass any more granular permissions you have. I would guess it is
more likely to be system objects that are causing the problems than user
ones. You could run profiler on the remote system to see what it does when
you have the permissions and where it stalls when you don't
John
"Doug" wrote:
> Any chance there was ever a resolution to this problem? We're having the
> same issue and I cannot find the answer.
> In our SQL 2000 server, it was a permissions problem to the sybase drivers
> folder. On the server, we had to give "Authenticated Users" read access to
> this folder. Once we did that, it worked. However, on our SQL 2005 server
> we did this same thing and the initialization error went away but now our
> queries just hang and never return. We have "Authenticated Users" in the
> Users group on the server but if we add them to the Administrators group then
> the queries run fine.
> Any ideas would be greatly appreciated.
> Thanks
>
|||Thanks for the reply!
You mentioned "sysadmin" but what I'm referring to is if we add the user to
the Administrators local group on the SQL server. It's not DB related at all
in my opinion, it's permissions at the O/S level related to the ODBC driver.
For example, on SQL 2000 we had to give the Users local group (which contains
NT Authenticated users such as in this case) on the server read permissions
to the folder that contains the Sybase ODBC driver otherwise we got an error.
We got that same error on the SQL 2005 and giving the User read permissions
to the Sybase ODBC driver folder got rid of that error but now the query just
hangs. However, if someone in the local Adminsstrators group runs the same
linked server query not only does it run fine for them, but it also runs fine
for the same the user that was previously getting an error. But it will only
work for a short period of time and then it stops working again. To me, what
seems to be happening is that when the person who is a local Admin on the
server runs the query it loads the driver into memory so that when the other
user runs the query it's already loaded. After a while, the driver gets
unloaded and then when the user runs the query again they have the problem.
It just seems related to OS permissions on the driver based on the behavior
we are seeing.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Doug
> SQL 2005 has a tighter security model so making them a sysadmin would
> probably bypass any more granular permissions you have. I would guess it is
> more likely to be system objects that are causing the problems than user
> ones. You could run profiler on the remote system to see what it does when
> you have the permissions and where it stalls when you don't
> John
> "Doug" wrote:
|||Hi
Local administrators are usually a sysadmin by default because of the
BUILTIN/Administrators group. I can't remember what I believed the problem
was in this post, but it may be that I though that if you were a sysadmin
the linked server ran it as yourself, but you weren't it ran it under the
service account, therefore the account which needed the directory permissions
depended on whether you were a sysadmin. I could have been wrong as I don't
know if this got resolved!
John
"Doug" wrote:
[vbcol=seagreen]
> Thanks for the reply!
> You mentioned "sysadmin" but what I'm referring to is if we add the user to
> the Administrators local group on the SQL server. It's not DB related at all
> in my opinion, it's permissions at the O/S level related to the ODBC driver.
> For example, on SQL 2000 we had to give the Users local group (which contains
> NT Authenticated users such as in this case) on the server read permissions
> to the folder that contains the Sybase ODBC driver otherwise we got an error.
> We got that same error on the SQL 2005 and giving the User read permissions
> to the Sybase ODBC driver folder got rid of that error but now the query just
> hangs. However, if someone in the local Adminsstrators group runs the same
> linked server query not only does it run fine for them, but it also runs fine
> for the same the user that was previously getting an error. But it will only
> work for a short period of time and then it stops working again. To me, what
> seems to be happening is that when the person who is a local Admin on the
> server runs the query it loads the driver into memory so that when the other
> user runs the query it's already loaded. After a while, the driver gets
> unloaded and then when the user runs the query again they have the problem.
> It just seems related to OS permissions on the driver based on the behavior
> we are seeing.
> "John Bell" wrote:
same issue and I cannot find the answer.
In our SQL 2000 server, it was a permissions problem to the sybase drivers
folder. On the server, we had to give "Authenticated Users" read access to
this folder. Once we did that, it worked. However, on our SQL 2005 server
we did this same thing and the initialization error went away but now our
queries just hang and never return. We have "Authenticated Users" in the
Users group on the server but if we add them to the Administrators group then
the queries run fine.
Any ideas would be greatly appreciated.
Thanks
Hi Doug
SQL 2005 has a tighter security model so making them a sysadmin would
probably bypass any more granular permissions you have. I would guess it is
more likely to be system objects that are causing the problems than user
ones. You could run profiler on the remote system to see what it does when
you have the permissions and where it stalls when you don't
John
"Doug" wrote:
> Any chance there was ever a resolution to this problem? We're having the
> same issue and I cannot find the answer.
> In our SQL 2000 server, it was a permissions problem to the sybase drivers
> folder. On the server, we had to give "Authenticated Users" read access to
> this folder. Once we did that, it worked. However, on our SQL 2005 server
> we did this same thing and the initialization error went away but now our
> queries just hang and never return. We have "Authenticated Users" in the
> Users group on the server but if we add them to the Administrators group then
> the queries run fine.
> Any ideas would be greatly appreciated.
> Thanks
>
|||Thanks for the reply!
You mentioned "sysadmin" but what I'm referring to is if we add the user to
the Administrators local group on the SQL server. It's not DB related at all
in my opinion, it's permissions at the O/S level related to the ODBC driver.
For example, on SQL 2000 we had to give the Users local group (which contains
NT Authenticated users such as in this case) on the server read permissions
to the folder that contains the Sybase ODBC driver otherwise we got an error.
We got that same error on the SQL 2005 and giving the User read permissions
to the Sybase ODBC driver folder got rid of that error but now the query just
hangs. However, if someone in the local Adminsstrators group runs the same
linked server query not only does it run fine for them, but it also runs fine
for the same the user that was previously getting an error. But it will only
work for a short period of time and then it stops working again. To me, what
seems to be happening is that when the person who is a local Admin on the
server runs the query it loads the driver into memory so that when the other
user runs the query it's already loaded. After a while, the driver gets
unloaded and then when the user runs the query again they have the problem.
It just seems related to OS permissions on the driver based on the behavior
we are seeing.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Doug
> SQL 2005 has a tighter security model so making them a sysadmin would
> probably bypass any more granular permissions you have. I would guess it is
> more likely to be system objects that are causing the problems than user
> ones. You could run profiler on the remote system to see what it does when
> you have the permissions and where it stalls when you don't
> John
> "Doug" wrote:
|||Hi
Local administrators are usually a sysadmin by default because of the
BUILTIN/Administrators group. I can't remember what I believed the problem
was in this post, but it may be that I though that if you were a sysadmin
the linked server ran it as yourself, but you weren't it ran it under the
service account, therefore the account which needed the directory permissions
depended on whether you were a sysadmin. I could have been wrong as I don't
know if this got resolved!
John
"Doug" wrote:
[vbcol=seagreen]
> Thanks for the reply!
> You mentioned "sysadmin" but what I'm referring to is if we add the user to
> the Administrators local group on the SQL server. It's not DB related at all
> in my opinion, it's permissions at the O/S level related to the ODBC driver.
> For example, on SQL 2000 we had to give the Users local group (which contains
> NT Authenticated users such as in this case) on the server read permissions
> to the folder that contains the Sybase ODBC driver otherwise we got an error.
> We got that same error on the SQL 2005 and giving the User read permissions
> to the Sybase ODBC driver folder got rid of that error but now the query just
> hangs. However, if someone in the local Adminsstrators group runs the same
> linked server query not only does it run fine for them, but it also runs fine
> for the same the user that was previously getting an error. But it will only
> work for a short period of time and then it stops working again. To me, what
> seems to be happening is that when the person who is a local Admin on the
> server runs the query it loads the driver into memory so that when the other
> user runs the query it's already loaded. After a while, the driver gets
> unloaded and then when the user runs the query again they have the problem.
> It just seems related to OS permissions on the driver based on the behavior
> we are seeing.
> "John Bell" wrote:
Monday, February 20, 2012
Linked server - admin vs non-admin windows login
Two windows logins - one admin and another - non-admin, are both setup in
sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
Jet OLEDB 4.0).
However while ADMIN login CAN successfully execute SELECT queries from this
linked server, non-admin can not, generating:
<QUOTE>
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"linked_server_1" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
</QUOTE>
Any suggestions re: possible cause?
All replies are very welcome.Hi
This look like a permissions issue with the non-administrator account. This
may help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
John
"Lolik" wrote:
> Two windows logins - one admin and another - non-admin, are both setup in
> sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
> Jet OLEDB 4.0).
> However while ADMIN login CAN successfully execute SELECT queries from this
> linked server, non-admin can not, generating:
> <QUOTE>
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
> "linked_server_1" returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
> </QUOTE>
> Any suggestions re: possible cause?
> All replies are very welcome.|||Thanks for the link.
What could I do with permissions though?
It seems it is necessary for OLEDB to access some folder where non-admin
login has insufficient permissions.
Any ideas what they are?
"John Bell" wrote:
> Hi
> This look like a permissions issue with the non-administrator account. This
> may help:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
> John|||Hi
The may help further, it is not necessarilly the directory where the access
database is but the temp directory for the service account.
http://support.microsoft.com/kb/814398
I would also look at KB 285833 "PRB: Need to map to default Admin account
and use NULL for password to query linked server to Access database", but
this does not seem to be available. I think it says run a command similar to
the following: http://tinyurl.com/ox9h7
John
"Lolik" wrote:
> Thanks for the link.
> What could I do with permissions though?
> It seems it is necessary for OLEDB to access some folder where non-admin
> login has insufficient permissions.
> Any ideas what they are?
>
> "John Bell" wrote:
> > Hi
> >
> > This look like a permissions issue with the non-administrator account. This
> > may help:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
> >
> > John|||Tried the Temp/Tmp trick, it did not work.
Could there be any other directories where the non-admin a/c would not have
the necessary permissions?
I did run sp_addlinkedsrvlogin command. The linked server works OK under
admin login.
I needed the linked server for development, not production. So it is not a
major problem for me at the moment.
Many thanks for your help!
"John Bell" wrote:
> Hi
> The may help further, it is not necessarilly the directory where the access
> database is but the temp directory for the service account.
> http://support.microsoft.com/kb/814398
> I would also look at KB 285833 "PRB: Need to map to default Admin account
> and use NULL for password to query linked server to Access database", but
> this does not seem to be available. I think it says run a command similar to
> the following: http://tinyurl.com/ox9h7
> John|||Hi
Assuming that you have added permissions to the database then there should
only be the directory in which it resides. Have you tried logging in as the
service account to access the access database?
John
"Lolik" wrote:
> Tried the Temp/Tmp trick, it did not work.
> Could there be any other directories where the non-admin a/c would not have
> the necessary permissions?
> I did run sp_addlinkedsrvlogin command. The linked server works OK under
> admin login.
> I needed the linked server for development, not production. So it is not a
> major problem for me at the moment.
> Many thanks for your help!
>
> "John Bell" wrote:
> > Hi
> >
> > The may help further, it is not necessarilly the directory where the access
> > database is but the temp directory for the service account.
> >
> > http://support.microsoft.com/kb/814398
> >
> > I would also look at KB 285833 "PRB: Need to map to default Admin account
> > and use NULL for password to query linked server to Access database", but
> > this does not seem to be available. I think it says run a command similar to
> > the following: http://tinyurl.com/ox9h7
> >
> > John
>|||"logging in as the service account "
- do you mean log in to SQL Server Management Studio as a service a/c?
How would I do this? There is only choice between Windows login and SQL
Server logins when connecting to SQL Server Management Studio ..
"John Bell" wrote:
> Hi
> Assuming that you have added permissions to the database then there should
> only be the directory in which it resides. Have you tried logging in as the
> service account to access the access database?
> John
>|||Hi
The service account is the windows account that the service is set to run
under, you can see this in the configuration tool or the services applet on
the administrators menu. You may have it configured as local system, in which
case using a domain account may help. If it is already a domain account use
this account to log into the server and open the access database.
Requirements for a service account are detailed in Books Online or at
http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
"Lolik" wrote:
> "logging in as the service account "
> - do you mean log in to SQL Server Management Studio as a service a/c?
> How would I do this? There is only choice between Windows login and SQL
> Server logins when connecting to SQL Server Management Studio ..
>
> "John Bell" wrote:
> > Hi
> >
> > Assuming that you have added permissions to the database then there should
> > only be the directory in which it resides. Have you tried logging in as the
> > service account to access the access database?
> >
> > John
> >
>|||The server runs on a desktop (not on a domain or any network altogether)..
I think I understood you:
when setting up the .mdb file as a linked server, I specified 'Admin' in
sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
name]\[user a/c login]' with a NULL password instead?
"John Bell" wrote:
> Hi
> The service account is the windows account that the service is set to run
> under, you can see this in the configuration tool or the services applet on
> the administrators menu. You may have it configured as local system, in which
> case using a domain account may help. If it is already a domain account use
> this account to log into the server and open the access database.
> Requirements for a service account are detailed in Books Online or at
> http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> John|||Hi
Usually you would not have to map windows logins with sp_addlinkedsrvlogin
as there should be a default mapping see sp_addlinkedsrvlogin in Books
Online. What does the acount information say on your service?
John
"Lolik" wrote:
> The server runs on a desktop (not on a domain or any network altogether)..
> I think I understood you:
> when setting up the .mdb file as a linked server, I specified 'Admin' in
> sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
> name]\[user a/c login]' with a NULL password instead?
>
> "John Bell" wrote:
> > Hi
> >
> > The service account is the windows account that the service is set to run
> > under, you can see this in the configuration tool or the services applet on
> > the administrators menu. You may have it configured as local system, in which
> > case using a domain account may help. If it is already a domain account use
> > this account to log into the server and open the access database.
> > Requirements for a service account are detailed in Books Online or at
> > http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> >
> > John
>|||The server runs as a network service..
"John Bell" wrote:
> Hi
> Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> as there should be a default mapping see sp_addlinkedsrvlogin in Books
> Online. What does the acount information say on your service?
> John|||Hi
But in the services applet if you double click the SQL Server service what
does it say on the log on tab?
John
"Lolik" wrote:
> The server runs as a network service..
>
> "John Bell" wrote:
> > Hi
> >
> > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > Online. What does the acount information say on your service?
> >
> > John
>|||if i go to SQL Server Configuration Manager..
..SQL Server 2005 Services
.. SQL Server (MSSQLSERVER)
and double-click on it,
a) the log on tab says:
Log on as:
built-in account:
Network Service
b) Service tab says:
Name SQL Server (MSSQLSERVER)
SQL Service Type SQL Server
"John Bell" wrote:
> Hi
> But in the services applet if you double click the SQL Server service what
> does it say on the log on tab?
> John
> "Lolik" wrote:
> > The server runs as a network service..
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > > Online. What does the acount information say on your service?
> > >
> > > John
> >
> >|||Hi
This may be related to your other problem of not being able to change the
service accounts.
John
"Lolik" wrote:
> if i go to SQL Server Configuration Manager..
> ..SQL Server 2005 Services
> .. SQL Server (MSSQLSERVER)
> and double-click on it,
> a) the log on tab says:
> Log on as:
> built-in account:
> Network Service
> b) Service tab says:
> Name SQL Server (MSSQLSERVER)
> SQL Service Type SQL Server
>
> "John Bell" wrote:
> > Hi
> >
> > But in the services applet if you double click the SQL Server service what
> > does it say on the log on tab?
> >
> > John
> >
> > "Lolik" wrote:
> >
> > > The server runs as a network service..
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > > > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > > > Online. What does the acount information say on your service?
> > > >
> > > > John
> > >
> > >|||Any chance there was ever a resolution to this problem? We're having the
same issue and I cannot find the answer.
In our SQL 2000 server, it was a permissions problem to the sybase drivers
folder. On the server, we had to give "Authenticated Users" read access to
this folder. Once we did that, it worked. However, on our SQL 2005 server
we did this same thing and the initialization error went away but now our
queries just hang and never return. We have "Authenticated Users" in the
Users group on the server but if we add them to the Administrators group then
the queries run fine.
Any ideas would be greatly appreciated.
Thanks|||Hi Doug
SQL 2005 has a tighter security model so making them a sysadmin would
probably bypass any more granular permissions you have. I would guess it is
more likely to be system objects that are causing the problems than user
ones. You could run profiler on the remote system to see what it does when
you have the permissions and where it stalls when you don't
John
"Doug" wrote:
> Any chance there was ever a resolution to this problem? We're having the
> same issue and I cannot find the answer.
> In our SQL 2000 server, it was a permissions problem to the sybase drivers
> folder. On the server, we had to give "Authenticated Users" read access to
> this folder. Once we did that, it worked. However, on our SQL 2005 server
> we did this same thing and the initialization error went away but now our
> queries just hang and never return. We have "Authenticated Users" in the
> Users group on the server but if we add them to the Administrators group then
> the queries run fine.
> Any ideas would be greatly appreciated.
> Thanks
>|||Thanks for the reply!
You mentioned "sysadmin" but what I'm referring to is if we add the user to
the Administrators local group on the SQL server. It's not DB related at all
in my opinion, it's permissions at the O/S level related to the ODBC driver.
For example, on SQL 2000 we had to give the Users local group (which contains
NT Authenticated users such as in this case) on the server read permissions
to the folder that contains the Sybase ODBC driver otherwise we got an error.
We got that same error on the SQL 2005 and giving the User read permissions
to the Sybase ODBC driver folder got rid of that error but now the query just
hangs. However, if someone in the local Adminsstrators group runs the same
linked server query not only does it run fine for them, but it also runs fine
for the same the user that was previously getting an error. But it will only
work for a short period of time and then it stops working again. To me, what
seems to be happening is that when the person who is a local Admin on the
server runs the query it loads the driver into memory so that when the other
user runs the query it's already loaded. After a while, the driver gets
unloaded and then when the user runs the query again they have the problem.
It just seems related to OS permissions on the driver based on the behavior
we are seeing.
"John Bell" wrote:
> Hi Doug
> SQL 2005 has a tighter security model so making them a sysadmin would
> probably bypass any more granular permissions you have. I would guess it is
> more likely to be system objects that are causing the problems than user
> ones. You could run profiler on the remote system to see what it does when
> you have the permissions and where it stalls when you don't
> John
> "Doug" wrote:
> > Any chance there was ever a resolution to this problem? We're having the
> > same issue and I cannot find the answer.
> >
> > In our SQL 2000 server, it was a permissions problem to the sybase drivers
> > folder. On the server, we had to give "Authenticated Users" read access to
> > this folder. Once we did that, it worked. However, on our SQL 2005 server
> > we did this same thing and the initialization error went away but now our
> > queries just hang and never return. We have "Authenticated Users" in the
> > Users group on the server but if we add them to the Administrators group then
> > the queries run fine.
> >
> > Any ideas would be greatly appreciated.
> > Thanks
> >
> >|||Hi
Local administrators are usually a sysadmin by default because of the
BUILTIN/Administrators group. I can't remember what I believed the problem
was in this post, but it may be that I though that if you were a sysadmin
the linked server ran it as yourself, but you weren't it ran it under the
service account, therefore the account which needed the directory permissions
depended on whether you were a sysadmin. I could have been wrong as I don't
know if this got resolved!
John
"Doug" wrote:
> Thanks for the reply!
> You mentioned "sysadmin" but what I'm referring to is if we add the user to
> the Administrators local group on the SQL server. It's not DB related at all
> in my opinion, it's permissions at the O/S level related to the ODBC driver.
> For example, on SQL 2000 we had to give the Users local group (which contains
> NT Authenticated users such as in this case) on the server read permissions
> to the folder that contains the Sybase ODBC driver otherwise we got an error.
> We got that same error on the SQL 2005 and giving the User read permissions
> to the Sybase ODBC driver folder got rid of that error but now the query just
> hangs. However, if someone in the local Adminsstrators group runs the same
> linked server query not only does it run fine for them, but it also runs fine
> for the same the user that was previously getting an error. But it will only
> work for a short period of time and then it stops working again. To me, what
> seems to be happening is that when the person who is a local Admin on the
> server runs the query it loads the driver into memory so that when the other
> user runs the query it's already loaded. After a while, the driver gets
> unloaded and then when the user runs the query again they have the problem.
> It just seems related to OS permissions on the driver based on the behavior
> we are seeing.
> "John Bell" wrote:
> > Hi Doug
> >
> > SQL 2005 has a tighter security model so making them a sysadmin would
> > probably bypass any more granular permissions you have. I would guess it is
> > more likely to be system objects that are causing the problems than user
> > ones. You could run profiler on the remote system to see what it does when
> > you have the permissions and where it stalls when you don't
> >
> > John
> >
> > "Doug" wrote:
> >
> > > Any chance there was ever a resolution to this problem? We're having the
> > > same issue and I cannot find the answer.
> > >
> > > In our SQL 2000 server, it was a permissions problem to the sybase drivers
> > > folder. On the server, we had to give "Authenticated Users" read access to
> > > this folder. Once we did that, it worked. However, on our SQL 2005 server
> > > we did this same thing and the initialization error went away but now our
> > > queries just hang and never return. We have "Authenticated Users" in the
> > > Users group on the server but if we add them to the Administrators group then
> > > the queries run fine.
> > >
> > > Any ideas would be greatly appreciated.
> > > Thanks
> > >
> > >
sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
Jet OLEDB 4.0).
However while ADMIN login CAN successfully execute SELECT queries from this
linked server, non-admin can not, generating:
<QUOTE>
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"linked_server_1" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
</QUOTE>
Any suggestions re: possible cause?
All replies are very welcome.Hi
This look like a permissions issue with the non-administrator account. This
may help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
John
"Lolik" wrote:
> Two windows logins - one admin and another - non-admin, are both setup in
> sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
> Jet OLEDB 4.0).
> However while ADMIN login CAN successfully execute SELECT queries from this
> linked server, non-admin can not, generating:
> <QUOTE>
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
> "linked_server_1" returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
> </QUOTE>
> Any suggestions re: possible cause?
> All replies are very welcome.|||Thanks for the link.
What could I do with permissions though?
It seems it is necessary for OLEDB to access some folder where non-admin
login has insufficient permissions.
Any ideas what they are?
"John Bell" wrote:
> Hi
> This look like a permissions issue with the non-administrator account. This
> may help:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
> John|||Hi
The may help further, it is not necessarilly the directory where the access
database is but the temp directory for the service account.
http://support.microsoft.com/kb/814398
I would also look at KB 285833 "PRB: Need to map to default Admin account
and use NULL for password to query linked server to Access database", but
this does not seem to be available. I think it says run a command similar to
the following: http://tinyurl.com/ox9h7
John
"Lolik" wrote:
> Thanks for the link.
> What could I do with permissions though?
> It seems it is necessary for OLEDB to access some folder where non-admin
> login has insufficient permissions.
> Any ideas what they are?
>
> "John Bell" wrote:
> > Hi
> >
> > This look like a permissions issue with the non-administrator account. This
> > may help:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
> >
> > John|||Tried the Temp/Tmp trick, it did not work.
Could there be any other directories where the non-admin a/c would not have
the necessary permissions?
I did run sp_addlinkedsrvlogin command. The linked server works OK under
admin login.
I needed the linked server for development, not production. So it is not a
major problem for me at the moment.
Many thanks for your help!
"John Bell" wrote:
> Hi
> The may help further, it is not necessarilly the directory where the access
> database is but the temp directory for the service account.
> http://support.microsoft.com/kb/814398
> I would also look at KB 285833 "PRB: Need to map to default Admin account
> and use NULL for password to query linked server to Access database", but
> this does not seem to be available. I think it says run a command similar to
> the following: http://tinyurl.com/ox9h7
> John|||Hi
Assuming that you have added permissions to the database then there should
only be the directory in which it resides. Have you tried logging in as the
service account to access the access database?
John
"Lolik" wrote:
> Tried the Temp/Tmp trick, it did not work.
> Could there be any other directories where the non-admin a/c would not have
> the necessary permissions?
> I did run sp_addlinkedsrvlogin command. The linked server works OK under
> admin login.
> I needed the linked server for development, not production. So it is not a
> major problem for me at the moment.
> Many thanks for your help!
>
> "John Bell" wrote:
> > Hi
> >
> > The may help further, it is not necessarilly the directory where the access
> > database is but the temp directory for the service account.
> >
> > http://support.microsoft.com/kb/814398
> >
> > I would also look at KB 285833 "PRB: Need to map to default Admin account
> > and use NULL for password to query linked server to Access database", but
> > this does not seem to be available. I think it says run a command similar to
> > the following: http://tinyurl.com/ox9h7
> >
> > John
>|||"logging in as the service account "
- do you mean log in to SQL Server Management Studio as a service a/c?
How would I do this? There is only choice between Windows login and SQL
Server logins when connecting to SQL Server Management Studio ..
"John Bell" wrote:
> Hi
> Assuming that you have added permissions to the database then there should
> only be the directory in which it resides. Have you tried logging in as the
> service account to access the access database?
> John
>|||Hi
The service account is the windows account that the service is set to run
under, you can see this in the configuration tool or the services applet on
the administrators menu. You may have it configured as local system, in which
case using a domain account may help. If it is already a domain account use
this account to log into the server and open the access database.
Requirements for a service account are detailed in Books Online or at
http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
"Lolik" wrote:
> "logging in as the service account "
> - do you mean log in to SQL Server Management Studio as a service a/c?
> How would I do this? There is only choice between Windows login and SQL
> Server logins when connecting to SQL Server Management Studio ..
>
> "John Bell" wrote:
> > Hi
> >
> > Assuming that you have added permissions to the database then there should
> > only be the directory in which it resides. Have you tried logging in as the
> > service account to access the access database?
> >
> > John
> >
>|||The server runs on a desktop (not on a domain or any network altogether)..
I think I understood you:
when setting up the .mdb file as a linked server, I specified 'Admin' in
sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
name]\[user a/c login]' with a NULL password instead?
"John Bell" wrote:
> Hi
> The service account is the windows account that the service is set to run
> under, you can see this in the configuration tool or the services applet on
> the administrators menu. You may have it configured as local system, in which
> case using a domain account may help. If it is already a domain account use
> this account to log into the server and open the access database.
> Requirements for a service account are detailed in Books Online or at
> http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> John|||Hi
Usually you would not have to map windows logins with sp_addlinkedsrvlogin
as there should be a default mapping see sp_addlinkedsrvlogin in Books
Online. What does the acount information say on your service?
John
"Lolik" wrote:
> The server runs on a desktop (not on a domain or any network altogether)..
> I think I understood you:
> when setting up the .mdb file as a linked server, I specified 'Admin' in
> sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
> name]\[user a/c login]' with a NULL password instead?
>
> "John Bell" wrote:
> > Hi
> >
> > The service account is the windows account that the service is set to run
> > under, you can see this in the configuration tool or the services applet on
> > the administrators menu. You may have it configured as local system, in which
> > case using a domain account may help. If it is already a domain account use
> > this account to log into the server and open the access database.
> > Requirements for a service account are detailed in Books Online or at
> > http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> >
> > John
>|||The server runs as a network service..
"John Bell" wrote:
> Hi
> Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> as there should be a default mapping see sp_addlinkedsrvlogin in Books
> Online. What does the acount information say on your service?
> John|||Hi
But in the services applet if you double click the SQL Server service what
does it say on the log on tab?
John
"Lolik" wrote:
> The server runs as a network service..
>
> "John Bell" wrote:
> > Hi
> >
> > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > Online. What does the acount information say on your service?
> >
> > John
>|||if i go to SQL Server Configuration Manager..
..SQL Server 2005 Services
.. SQL Server (MSSQLSERVER)
and double-click on it,
a) the log on tab says:
Log on as:
built-in account:
Network Service
b) Service tab says:
Name SQL Server (MSSQLSERVER)
SQL Service Type SQL Server
"John Bell" wrote:
> Hi
> But in the services applet if you double click the SQL Server service what
> does it say on the log on tab?
> John
> "Lolik" wrote:
> > The server runs as a network service..
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > > Online. What does the acount information say on your service?
> > >
> > > John
> >
> >|||Hi
This may be related to your other problem of not being able to change the
service accounts.
John
"Lolik" wrote:
> if i go to SQL Server Configuration Manager..
> ..SQL Server 2005 Services
> .. SQL Server (MSSQLSERVER)
> and double-click on it,
> a) the log on tab says:
> Log on as:
> built-in account:
> Network Service
> b) Service tab says:
> Name SQL Server (MSSQLSERVER)
> SQL Service Type SQL Server
>
> "John Bell" wrote:
> > Hi
> >
> > But in the services applet if you double click the SQL Server service what
> > does it say on the log on tab?
> >
> > John
> >
> > "Lolik" wrote:
> >
> > > The server runs as a network service..
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > > > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > > > Online. What does the acount information say on your service?
> > > >
> > > > John
> > >
> > >|||Any chance there was ever a resolution to this problem? We're having the
same issue and I cannot find the answer.
In our SQL 2000 server, it was a permissions problem to the sybase drivers
folder. On the server, we had to give "Authenticated Users" read access to
this folder. Once we did that, it worked. However, on our SQL 2005 server
we did this same thing and the initialization error went away but now our
queries just hang and never return. We have "Authenticated Users" in the
Users group on the server but if we add them to the Administrators group then
the queries run fine.
Any ideas would be greatly appreciated.
Thanks|||Hi Doug
SQL 2005 has a tighter security model so making them a sysadmin would
probably bypass any more granular permissions you have. I would guess it is
more likely to be system objects that are causing the problems than user
ones. You could run profiler on the remote system to see what it does when
you have the permissions and where it stalls when you don't
John
"Doug" wrote:
> Any chance there was ever a resolution to this problem? We're having the
> same issue and I cannot find the answer.
> In our SQL 2000 server, it was a permissions problem to the sybase drivers
> folder. On the server, we had to give "Authenticated Users" read access to
> this folder. Once we did that, it worked. However, on our SQL 2005 server
> we did this same thing and the initialization error went away but now our
> queries just hang and never return. We have "Authenticated Users" in the
> Users group on the server but if we add them to the Administrators group then
> the queries run fine.
> Any ideas would be greatly appreciated.
> Thanks
>|||Thanks for the reply!
You mentioned "sysadmin" but what I'm referring to is if we add the user to
the Administrators local group on the SQL server. It's not DB related at all
in my opinion, it's permissions at the O/S level related to the ODBC driver.
For example, on SQL 2000 we had to give the Users local group (which contains
NT Authenticated users such as in this case) on the server read permissions
to the folder that contains the Sybase ODBC driver otherwise we got an error.
We got that same error on the SQL 2005 and giving the User read permissions
to the Sybase ODBC driver folder got rid of that error but now the query just
hangs. However, if someone in the local Adminsstrators group runs the same
linked server query not only does it run fine for them, but it also runs fine
for the same the user that was previously getting an error. But it will only
work for a short period of time and then it stops working again. To me, what
seems to be happening is that when the person who is a local Admin on the
server runs the query it loads the driver into memory so that when the other
user runs the query it's already loaded. After a while, the driver gets
unloaded and then when the user runs the query again they have the problem.
It just seems related to OS permissions on the driver based on the behavior
we are seeing.
"John Bell" wrote:
> Hi Doug
> SQL 2005 has a tighter security model so making them a sysadmin would
> probably bypass any more granular permissions you have. I would guess it is
> more likely to be system objects that are causing the problems than user
> ones. You could run profiler on the remote system to see what it does when
> you have the permissions and where it stalls when you don't
> John
> "Doug" wrote:
> > Any chance there was ever a resolution to this problem? We're having the
> > same issue and I cannot find the answer.
> >
> > In our SQL 2000 server, it was a permissions problem to the sybase drivers
> > folder. On the server, we had to give "Authenticated Users" read access to
> > this folder. Once we did that, it worked. However, on our SQL 2005 server
> > we did this same thing and the initialization error went away but now our
> > queries just hang and never return. We have "Authenticated Users" in the
> > Users group on the server but if we add them to the Administrators group then
> > the queries run fine.
> >
> > Any ideas would be greatly appreciated.
> > Thanks
> >
> >|||Hi
Local administrators are usually a sysadmin by default because of the
BUILTIN/Administrators group. I can't remember what I believed the problem
was in this post, but it may be that I though that if you were a sysadmin
the linked server ran it as yourself, but you weren't it ran it under the
service account, therefore the account which needed the directory permissions
depended on whether you were a sysadmin. I could have been wrong as I don't
know if this got resolved!
John
"Doug" wrote:
> Thanks for the reply!
> You mentioned "sysadmin" but what I'm referring to is if we add the user to
> the Administrators local group on the SQL server. It's not DB related at all
> in my opinion, it's permissions at the O/S level related to the ODBC driver.
> For example, on SQL 2000 we had to give the Users local group (which contains
> NT Authenticated users such as in this case) on the server read permissions
> to the folder that contains the Sybase ODBC driver otherwise we got an error.
> We got that same error on the SQL 2005 and giving the User read permissions
> to the Sybase ODBC driver folder got rid of that error but now the query just
> hangs. However, if someone in the local Adminsstrators group runs the same
> linked server query not only does it run fine for them, but it also runs fine
> for the same the user that was previously getting an error. But it will only
> work for a short period of time and then it stops working again. To me, what
> seems to be happening is that when the person who is a local Admin on the
> server runs the query it loads the driver into memory so that when the other
> user runs the query it's already loaded. After a while, the driver gets
> unloaded and then when the user runs the query again they have the problem.
> It just seems related to OS permissions on the driver based on the behavior
> we are seeing.
> "John Bell" wrote:
> > Hi Doug
> >
> > SQL 2005 has a tighter security model so making them a sysadmin would
> > probably bypass any more granular permissions you have. I would guess it is
> > more likely to be system objects that are causing the problems than user
> > ones. You could run profiler on the remote system to see what it does when
> > you have the permissions and where it stalls when you don't
> >
> > John
> >
> > "Doug" wrote:
> >
> > > Any chance there was ever a resolution to this problem? We're having the
> > > same issue and I cannot find the answer.
> > >
> > > In our SQL 2000 server, it was a permissions problem to the sybase drivers
> > > folder. On the server, we had to give "Authenticated Users" read access to
> > > this folder. Once we did that, it worked. However, on our SQL 2005 server
> > > we did this same thing and the initialization error went away but now our
> > > queries just hang and never return. We have "Authenticated Users" in the
> > > Users group on the server but if we add them to the Administrators group then
> > > the queries run fine.
> > >
> > > Any ideas would be greatly appreciated.
> > > Thanks
> > >
> > >
Linked server - admin vs non-admin windows login
Two windows logins - one admin and another - non-admin, are both setup in
sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
Jet OLEDB 4.0).
However while ADMIN login CAN successfully execute SELECT queries from this
linked server, non-admin can not, generating:
<QUOTE>
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"linked_server_1" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
</QUOTE>
Any suggestions re: possible cause?
All replies are very welcome.Hi
This look like a permissions issue with the non-administrator account. This
may help:
http://msdn.microsoft.com/library/d...serr_2_5dx5.asp
John
"Lolik" wrote:
> Two windows logins - one admin and another - non-admin, are both setup in
> sysadmin roles. Both can create a linked server (e.g. Access .mdb file usi
ng
> Jet OLEDB 4.0).
> However while ADMIN login CAN successfully execute SELECT queries from thi
s
> linked server, non-admin can not, generating:
> <QUOTE>
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
> "linked_server_1" returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
> </QUOTE>
> Any suggestions re: possible cause?
> All replies are very welcome.|||Thanks for the link.
What could I do with permissions though?
It seems it is necessary for OLEDB to access some folder where non-admin
login has insufficient permissions.
Any ideas what they are?
"John Bell" wrote:
> Hi
> This look like a permissions issue with the non-administrator account. Thi
s
> may help:
> http://msdn.microsoft.com/library/d...serr_2_5dx5.asp
> John|||Hi
The may help further, it is not necessarilly the directory where the access
database is but the temp directory for the service account.
http://support.microsoft.com/kb/814398
I would also look at KB 285833 "PRB: Need to map to default Admin account
and use NULL for password to query linked server to Access database", but
this does not seem to be available. I think it says run a command similar to
the following: http://tinyurl.com/ox9h7
John
"Lolik" wrote:
[vbcol=seagreen]
> Thanks for the link.
> What could I do with permissions though?
> It seems it is necessary for OLEDB to access some folder where non-admin
> login has insufficient permissions.
> Any ideas what they are?
>
> "John Bell" wrote:
>|||Tried the Temp/Tmp trick, it did not work.
Could there be any other directories where the non-admin a/c would not have
the necessary permissions?
I did run sp_addlinkedsrvlogin command. The linked server works OK under
admin login.
I needed the linked server for development, not production. So it is not a
major problem for me at the moment.
Many thanks for your help!
"John Bell" wrote:
> Hi
> The may help further, it is not necessarilly the directory where the acces
s
> database is but the temp directory for the service account.
> http://support.microsoft.com/kb/814398
> I would also look at KB 285833 "PRB: Need to map to default Admin account
> and use NULL for password to query linked server to Access database", but
> this does not seem to be available. I think it says run a command similar
to
> the following: http://tinyurl.com/ox9h7
> John|||Hi
Assuming that you have added permissions to the database then there should
only be the directory in which it resides. Have you tried logging in as the
service account to access the access database?
John
"Lolik" wrote:
> Tried the Temp/Tmp trick, it did not work.
> Could there be any other directories where the non-admin a/c would not hav
e
> the necessary permissions?
> I did run sp_addlinkedsrvlogin command. The linked server works OK under
> admin login.
> I needed the linked server for development, not production. So it is not a
> major problem for me at the moment.
> Many thanks for your help!
>
> "John Bell" wrote:
>
>|||"logging in as the service account "
- do you mean log in to SQL Server Management Studio as a service a/c?
How would I do this? There is only choice between Windows login and SQL
Server logins when connecting to SQL Server Management Studio ..
"John Bell" wrote:
> Hi
> Assuming that you have added permissions to the database then there should
> only be the directory in which it resides. Have you tried logging in as th
e
> service account to access the access database?
> John
>|||Hi
The service account is the windows account that the service is set to run
under, you can see this in the configuration tool or the services applet on
the administrators menu. You may have it configured as local system, in whic
h
case using a domain account may help. If it is already a domain account use
this account to log into the server and open the access database.
Requirements for a service account are detailed in Books Online or at
http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
"Lolik" wrote:
> "logging in as the service account "
> - do you mean log in to SQL Server Management Studio as a service a/c?
> How would I do this? There is only choice between Windows login and SQL
> Server logins when connecting to SQL Server Management Studio ..
>
> "John Bell" wrote:
>
>|||The server runs on a desktop (not on a domain or any network altogether)..
I think I understood you:
when setting up the .mdb file as a linked server, I specified 'Admin' in
sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
name]\[user a/c login]' with a NULL password instead?
"John Bell" wrote:
> Hi
> The service account is the windows account that the service is set to run
> under, you can see this in the configuration tool or the services applet o
n
> the administrators menu. You may have it configured as local system, in wh
ich
> case using a domain account may help. If it is already a domain account us
e
> this account to log into the server and open the access database.
> Requirements for a service account are detailed in Books Online or at
> http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> John|||Hi
Usually you would not have to map windows logins with sp_addlinkedsrvlogin
as there should be a default mapping see sp_addlinkedsrvlogin in Books
Online. What does the acount information say on your service?
John
"Lolik" wrote:
> The server runs on a desktop (not on a domain or any network altogether)..
> I think I understood you:
> when setting up the .mdb file as a linked server, I specified 'Admin' in
> sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[
pc
> name]\[user a/c login]' with a NULL password instead?
>
> "John Bell" wrote:
>
>
sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
Jet OLEDB 4.0).
However while ADMIN login CAN successfully execute SELECT queries from this
linked server, non-admin can not, generating:
<QUOTE>
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"linked_server_1" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
</QUOTE>
Any suggestions re: possible cause?
All replies are very welcome.Hi
This look like a permissions issue with the non-administrator account. This
may help:
http://msdn.microsoft.com/library/d...serr_2_5dx5.asp
John
"Lolik" wrote:
> Two windows logins - one admin and another - non-admin, are both setup in
> sysadmin roles. Both can create a linked server (e.g. Access .mdb file usi
ng
> Jet OLEDB 4.0).
> However while ADMIN login CAN successfully execute SELECT queries from thi
s
> linked server, non-admin can not, generating:
> <QUOTE>
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
> "linked_server_1" returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
> </QUOTE>
> Any suggestions re: possible cause?
> All replies are very welcome.|||Thanks for the link.
What could I do with permissions though?
It seems it is necessary for OLEDB to access some folder where non-admin
login has insufficient permissions.
Any ideas what they are?
"John Bell" wrote:
> Hi
> This look like a permissions issue with the non-administrator account. Thi
s
> may help:
> http://msdn.microsoft.com/library/d...serr_2_5dx5.asp
> John|||Hi
The may help further, it is not necessarilly the directory where the access
database is but the temp directory for the service account.
http://support.microsoft.com/kb/814398
I would also look at KB 285833 "PRB: Need to map to default Admin account
and use NULL for password to query linked server to Access database", but
this does not seem to be available. I think it says run a command similar to
the following: http://tinyurl.com/ox9h7
John
"Lolik" wrote:
[vbcol=seagreen]
> Thanks for the link.
> What could I do with permissions though?
> It seems it is necessary for OLEDB to access some folder where non-admin
> login has insufficient permissions.
> Any ideas what they are?
>
> "John Bell" wrote:
>|||Tried the Temp/Tmp trick, it did not work.
Could there be any other directories where the non-admin a/c would not have
the necessary permissions?
I did run sp_addlinkedsrvlogin command. The linked server works OK under
admin login.
I needed the linked server for development, not production. So it is not a
major problem for me at the moment.
Many thanks for your help!
"John Bell" wrote:
> Hi
> The may help further, it is not necessarilly the directory where the acces
s
> database is but the temp directory for the service account.
> http://support.microsoft.com/kb/814398
> I would also look at KB 285833 "PRB: Need to map to default Admin account
> and use NULL for password to query linked server to Access database", but
> this does not seem to be available. I think it says run a command similar
to
> the following: http://tinyurl.com/ox9h7
> John|||Hi
Assuming that you have added permissions to the database then there should
only be the directory in which it resides. Have you tried logging in as the
service account to access the access database?
John
"Lolik" wrote:
> Tried the Temp/Tmp trick, it did not work.
> Could there be any other directories where the non-admin a/c would not hav
e
> the necessary permissions?
> I did run sp_addlinkedsrvlogin command. The linked server works OK under
> admin login.
> I needed the linked server for development, not production. So it is not a
> major problem for me at the moment.
> Many thanks for your help!
>
> "John Bell" wrote:
>
>|||"logging in as the service account "
- do you mean log in to SQL Server Management Studio as a service a/c?
How would I do this? There is only choice between Windows login and SQL
Server logins when connecting to SQL Server Management Studio ..
"John Bell" wrote:
> Hi
> Assuming that you have added permissions to the database then there should
> only be the directory in which it resides. Have you tried logging in as th
e
> service account to access the access database?
> John
>|||Hi
The service account is the windows account that the service is set to run
under, you can see this in the configuration tool or the services applet on
the administrators menu. You may have it configured as local system, in whic
h
case using a domain account may help. If it is already a domain account use
this account to log into the server and open the access database.
Requirements for a service account are detailed in Books Online or at
http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
"Lolik" wrote:
> "logging in as the service account "
> - do you mean log in to SQL Server Management Studio as a service a/c?
> How would I do this? There is only choice between Windows login and SQL
> Server logins when connecting to SQL Server Management Studio ..
>
> "John Bell" wrote:
>
>|||The server runs on a desktop (not on a domain or any network altogether)..
I think I understood you:
when setting up the .mdb file as a linked server, I specified 'Admin' in
sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
name]\[user a/c login]' with a NULL password instead?
"John Bell" wrote:
> Hi
> The service account is the windows account that the service is set to run
> under, you can see this in the configuration tool or the services applet o
n
> the administrators menu. You may have it configured as local system, in wh
ich
> case using a domain account may help. If it is already a domain account us
e
> this account to log into the server and open the access database.
> Requirements for a service account are detailed in Books Online or at
> http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> John|||Hi
Usually you would not have to map windows logins with sp_addlinkedsrvlogin
as there should be a default mapping see sp_addlinkedsrvlogin in Books
Online. What does the acount information say on your service?
John
"Lolik" wrote:
> The server runs on a desktop (not on a domain or any network altogether)..
> I think I understood you:
> when setting up the .mdb file as a linked server, I specified 'Admin' in
> sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[
pc
> name]\[user a/c login]' with a NULL password instead?
>
> "John Bell" wrote:
>
>
Subscribe to:
Posts (Atom)