I have two database servers with two different databases. I have the same
windows login set up on both database servers with read access to the two
different databases. I have linkedserver between the two database server.
When I query using a SQL login I get a result set. When I use the windows
login, I get an error - all else is equal.
Please help to resolve.
Hi
Next time, please post your error message too.
Look at
http://msdn.microsoft.com/library/de...urity_2gmm.asp
about what you need to setup for Impersonation.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"spoons" <spoons@.discussions.microsoft.com> wrote in message
news:38BF845C-A1E8-4264-8479-CE3C6DE2776B@.microsoft.com...
> I have two database servers with two different databases. I have the same
> windows login set up on both database servers with read access to the two
> different databases. I have linkedserver between the two database server.
> When I query using a SQL login I get a result set. When I use the windows
> login, I get an error - all else is equal.
> Please help to resolve.
Showing posts with label authentication. Show all posts
Showing posts with label authentication. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
servers using SQL Server Authentication
I am new to dealing with SQL Server permissions and security, so
hopefully the solution to my problem is straightforward. I have two SQL
Server 2005 Express databases that I want to link via SQL Server
Authentication (I cannot use Windows Authentication). The servers need
to be linked up initially via stored procedures (which will be called
via a VB.Net app). There is a security warning in BOL for
sp_addlinkedsrvlogin that says"
"This example does not use Windows Authentication. Passwords will be
transmitted unencrypted. Passwords may be visible in data source
definitions and scripts that are saved to disk, in backups, and in log
files. Never use an administrator password in this kind of connection.
Consult your network administrator for security guidance specific to
your environment."
Thus, I do not want to link the servers using the 'sa' password, and
instead have created a limited privledge user (called 'junk' for now)
and assigned it the to the roles that I need: db_reader, db_writer,
db_ddladmin (some of the stored procs that this user will call need to
alter tables), and setupadmin (to allow linking a server).
So logged in as the user 'junk', the first step is actually linking the
servers:
exec master.dbo.sp_addlinkedserver @.server =
N'192.168.1.124\SQLEXPRESS', @.srvproduct=N'SQL Server'
Then I need to map the logins with the other server, which also has the
same user 'junk' with the same permissions:
exec master.dbo.sp_addlinkedsrvlogin
'192.168.1. 124\SQLEXPRESS','FALSE','junk','junk','m
yPassword'
However, when I attempt to execute this, I get the error:
"User does not have permission to perform this action".
BOL says that the permissions required for sp_addlinkedsrvlogin are
"ALTER ANY LOGIN". I'm not entirely sure what this means, but in any
case I execute GRANT on this user:
GRANT ALTER ANY LOGIN on junk
Now sp_addlinkedsrvlogin above works. Is this the only way I can link
servers using this limited priviledge account? It seems to me that I am
I opening up a security vulnerability if someone sniffs the junk
password and then can "ALTER ANY LOGIN" using this account. Is there
another way? Perhaps I could briefly GRANT the "alter any login", link
the server, and the revoke the priviledge.
Thanks for any comments,
MarcusHi
The security warning relates to the usage and presence of the linked server
and not the creation.
If you are not creating the linked server within your application then your
"junk" user does not need the extra permissions. Create the linked server an
d
logins as an administrator.
John
"Marcus" wrote:
> I am new to dealing with SQL Server permissions and security, so
> hopefully the solution to my problem is straightforward. I have two SQL
> Server 2005 Express databases that I want to link via SQL Server
> Authentication (I cannot use Windows Authentication). The servers need
> to be linked up initially via stored procedures (which will be called
> via a VB.Net app). There is a security warning in BOL for
> sp_addlinkedsrvlogin that says"
> "This example does not use Windows Authentication. Passwords will be
> transmitted unencrypted. Passwords may be visible in data source
> definitions and scripts that are saved to disk, in backups, and in log
> files. Never use an administrator password in this kind of connection.
> Consult your network administrator for security guidance specific to
> your environment."
> Thus, I do not want to link the servers using the 'sa' password, and
> instead have created a limited privledge user (called 'junk' for now)
> and assigned it the to the roles that I need: db_reader, db_writer,
> db_ddladmin (some of the stored procs that this user will call need to
> alter tables), and setupadmin (to allow linking a server).
> So logged in as the user 'junk', the first step is actually linking the
> servers:
> exec master.dbo.sp_addlinkedserver @.server =
> N'192.168.1.124\SQLEXPRESS', @.srvproduct=N'SQL Server'
> Then I need to map the logins with the other server, which also has the
> same user 'junk' with the same permissions:
> exec master.dbo.sp_addlinkedsrvlogin
> '192.168.1. 124\SQLEXPRESS','FALSE','junk','junk','m
yPassword'
> However, when I attempt to execute this, I get the error:
> "User does not have permission to perform this action".
> BOL says that the permissions required for sp_addlinkedsrvlogin are
> "ALTER ANY LOGIN". I'm not entirely sure what this means, but in any
> case I execute GRANT on this user:
> GRANT ALTER ANY LOGIN on junk
> Now sp_addlinkedsrvlogin above works. Is this the only way I can link
> servers using this limited priviledge account? It seems to me that I am
> I opening up a security vulnerability if someone sniffs the junk
> password and then can "ALTER ANY LOGIN" using this account. Is there
> another way? Perhaps I could briefly GRANT the "alter any login", link
> the server, and the revoke the priviledge.
> Thanks for any comments,
> Marcus
>|||Thanks for your reply, John. Actually, the linking of the servers DOES
need to happen in the application (VB.Net), and thus I do want the user
'junk' to be able to set up the linked server. It is reasonable do you
think to momentarily give it permissions for "GRANT ALTER ANY LOGIN",
and then once the servers are linked to revoke that priviledge?
Thanks,
Marcus
P.S. What do you think of the permissions I have assigned to this user?
It needs to read from and write to tables, perform ALTER table, and run
some stopred procedures and functions. It is currently assigned to
these roles:
- db_reader
- db_writer
- db_ddladmin (for performing ALTER)
- setupadmin (for linking the server)
Have I given it too much?
Cheers,
M.
The user needs|||Hi Marcus
The best method of keeping the application secure it to avoid creating the
link server in the application. It is not clear why this has to be so dynami
c
and can not be part of an installation (or restricted) process. Giving the
user database roles will be less secure than granting specific privileges to
given tables or it would be even better to restrict access though stored
procedures.
John
"Marcus" wrote:
> Thanks for your reply, John. Actually, the linking of the servers DOES
> need to happen in the application (VB.Net), and thus I do want the user
> 'junk' to be able to set up the linked server. It is reasonable do you
> think to momentarily give it permissions for "GRANT ALTER ANY LOGIN",
> and then once the servers are linked to revoke that priviledge?
> Thanks,
> Marcus
> P.S. What do you think of the permissions I have assigned to this user?
> It needs to read from and write to tables, perform ALTER table, and run
> some stopred procedures and functions. It is currently assigned to
> these roles:
> - db_reader
> - db_writer
> - db_ddladmin (for performing ALTER)
> - setupadmin (for linking the server)
> Have I given it too much?
> Cheers,
> M.
>
> The user needs
>|||Thanks for your comments, John. The user of the application will need
to specify what server to link to over TCP/IP (no windows
authentication possible). Thus, it seems that the application needs to
call the following 2 stored procedures:
- sp_addlinkedserverexec
- sp_addlinkedsrvlogin
For the procedure "sp_addlinkedsrvlogin" it requires a username and
password. As windows authentication is not possible, I need to use sql
server authentication which will send the user name and password in
clear text. I thus don't want to use and powerful account like 'sa' as
it might be sniffed. I am still not clear of any other way to do this
via ado.net on the server EXCEPT...
... I have just learned about SQL-DMO. I think this may solve my
problem as I can take care of linking the servers and creating users,
assigning permissions using this object. It also provides a bunch of
other functionality that I think will be helpful, like being able to
iterate through all the other SQL Servers on the network.
Yes, I agree that ultimately I will need to restrict access to the
database except through stored procedures or views. Currently there is
a legacy application that is hitting each table directly.
Cheers,
Marcus|||Hi Marcus
It is still not clear why this is not in a setup program. If you don't need
to do this functionality more than one then take it out of the main program
and put it into a program where they can use a different account to set it
up. This will be more secure. The login passed to sp_addlinkedsrvlogin does
not have to be the same as the current login.
John
"Marcus" wrote:
> Thanks for your comments, John. The user of the application will need
> to specify what server to link to over TCP/IP (no windows
> authentication possible). Thus, it seems that the application needs to
> call the following 2 stored procedures:
> - sp_addlinkedserverexec
> - sp_addlinkedsrvlogin
> For the procedure "sp_addlinkedsrvlogin" it requires a username and
> password. As windows authentication is not possible, I need to use sql
> server authentication which will send the user name and password in
> clear text. I thus don't want to use and powerful account like 'sa' as
> it might be sniffed. I am still not clear of any other way to do this
> via ado.net on the server EXCEPT...
> ... I have just learned about SQL-DMO. I think this may solve my
> problem as I can take care of linking the servers and creating users,
> assigning permissions using this object. It also provides a bunch of
> other functionality that I think will be helpful, like being able to
> iterate through all the other SQL Servers on the network.
> Yes, I agree that ultimately I will need to restrict access to the
> database except through stored procedures or views. Currently there is
> a legacy application that is hitting each table directly.
> Cheers,
> Marcus
>|||Hi, John. In my scenario, the user is permitted to link up different
servers at runtime. I think I need to rethink my security here. It
would like be better to use something along the lines of your
suggestion. Thanks for you help.
Marcus
hopefully the solution to my problem is straightforward. I have two SQL
Server 2005 Express databases that I want to link via SQL Server
Authentication (I cannot use Windows Authentication). The servers need
to be linked up initially via stored procedures (which will be called
via a VB.Net app). There is a security warning in BOL for
sp_addlinkedsrvlogin that says"
"This example does not use Windows Authentication. Passwords will be
transmitted unencrypted. Passwords may be visible in data source
definitions and scripts that are saved to disk, in backups, and in log
files. Never use an administrator password in this kind of connection.
Consult your network administrator for security guidance specific to
your environment."
Thus, I do not want to link the servers using the 'sa' password, and
instead have created a limited privledge user (called 'junk' for now)
and assigned it the to the roles that I need: db_reader, db_writer,
db_ddladmin (some of the stored procs that this user will call need to
alter tables), and setupadmin (to allow linking a server).
So logged in as the user 'junk', the first step is actually linking the
servers:
exec master.dbo.sp_addlinkedserver @.server =
N'192.168.1.124\SQLEXPRESS', @.srvproduct=N'SQL Server'
Then I need to map the logins with the other server, which also has the
same user 'junk' with the same permissions:
exec master.dbo.sp_addlinkedsrvlogin
'192.168.1. 124\SQLEXPRESS','FALSE','junk','junk','m
yPassword'
However, when I attempt to execute this, I get the error:
"User does not have permission to perform this action".
BOL says that the permissions required for sp_addlinkedsrvlogin are
"ALTER ANY LOGIN". I'm not entirely sure what this means, but in any
case I execute GRANT on this user:
GRANT ALTER ANY LOGIN on junk
Now sp_addlinkedsrvlogin above works. Is this the only way I can link
servers using this limited priviledge account? It seems to me that I am
I opening up a security vulnerability if someone sniffs the junk
password and then can "ALTER ANY LOGIN" using this account. Is there
another way? Perhaps I could briefly GRANT the "alter any login", link
the server, and the revoke the priviledge.
Thanks for any comments,
MarcusHi
The security warning relates to the usage and presence of the linked server
and not the creation.
If you are not creating the linked server within your application then your
"junk" user does not need the extra permissions. Create the linked server an
d
logins as an administrator.
John
"Marcus" wrote:
> I am new to dealing with SQL Server permissions and security, so
> hopefully the solution to my problem is straightforward. I have two SQL
> Server 2005 Express databases that I want to link via SQL Server
> Authentication (I cannot use Windows Authentication). The servers need
> to be linked up initially via stored procedures (which will be called
> via a VB.Net app). There is a security warning in BOL for
> sp_addlinkedsrvlogin that says"
> "This example does not use Windows Authentication. Passwords will be
> transmitted unencrypted. Passwords may be visible in data source
> definitions and scripts that are saved to disk, in backups, and in log
> files. Never use an administrator password in this kind of connection.
> Consult your network administrator for security guidance specific to
> your environment."
> Thus, I do not want to link the servers using the 'sa' password, and
> instead have created a limited privledge user (called 'junk' for now)
> and assigned it the to the roles that I need: db_reader, db_writer,
> db_ddladmin (some of the stored procs that this user will call need to
> alter tables), and setupadmin (to allow linking a server).
> So logged in as the user 'junk', the first step is actually linking the
> servers:
> exec master.dbo.sp_addlinkedserver @.server =
> N'192.168.1.124\SQLEXPRESS', @.srvproduct=N'SQL Server'
> Then I need to map the logins with the other server, which also has the
> same user 'junk' with the same permissions:
> exec master.dbo.sp_addlinkedsrvlogin
> '192.168.1. 124\SQLEXPRESS','FALSE','junk','junk','m
yPassword'
> However, when I attempt to execute this, I get the error:
> "User does not have permission to perform this action".
> BOL says that the permissions required for sp_addlinkedsrvlogin are
> "ALTER ANY LOGIN". I'm not entirely sure what this means, but in any
> case I execute GRANT on this user:
> GRANT ALTER ANY LOGIN on junk
> Now sp_addlinkedsrvlogin above works. Is this the only way I can link
> servers using this limited priviledge account? It seems to me that I am
> I opening up a security vulnerability if someone sniffs the junk
> password and then can "ALTER ANY LOGIN" using this account. Is there
> another way? Perhaps I could briefly GRANT the "alter any login", link
> the server, and the revoke the priviledge.
> Thanks for any comments,
> Marcus
>|||Thanks for your reply, John. Actually, the linking of the servers DOES
need to happen in the application (VB.Net), and thus I do want the user
'junk' to be able to set up the linked server. It is reasonable do you
think to momentarily give it permissions for "GRANT ALTER ANY LOGIN",
and then once the servers are linked to revoke that priviledge?
Thanks,
Marcus
P.S. What do you think of the permissions I have assigned to this user?
It needs to read from and write to tables, perform ALTER table, and run
some stopred procedures and functions. It is currently assigned to
these roles:
- db_reader
- db_writer
- db_ddladmin (for performing ALTER)
- setupadmin (for linking the server)
Have I given it too much?
Cheers,
M.
The user needs|||Hi Marcus
The best method of keeping the application secure it to avoid creating the
link server in the application. It is not clear why this has to be so dynami
c
and can not be part of an installation (or restricted) process. Giving the
user database roles will be less secure than granting specific privileges to
given tables or it would be even better to restrict access though stored
procedures.
John
"Marcus" wrote:
> Thanks for your reply, John. Actually, the linking of the servers DOES
> need to happen in the application (VB.Net), and thus I do want the user
> 'junk' to be able to set up the linked server. It is reasonable do you
> think to momentarily give it permissions for "GRANT ALTER ANY LOGIN",
> and then once the servers are linked to revoke that priviledge?
> Thanks,
> Marcus
> P.S. What do you think of the permissions I have assigned to this user?
> It needs to read from and write to tables, perform ALTER table, and run
> some stopred procedures and functions. It is currently assigned to
> these roles:
> - db_reader
> - db_writer
> - db_ddladmin (for performing ALTER)
> - setupadmin (for linking the server)
> Have I given it too much?
> Cheers,
> M.
>
> The user needs
>|||Thanks for your comments, John. The user of the application will need
to specify what server to link to over TCP/IP (no windows
authentication possible). Thus, it seems that the application needs to
call the following 2 stored procedures:
- sp_addlinkedserverexec
- sp_addlinkedsrvlogin
For the procedure "sp_addlinkedsrvlogin" it requires a username and
password. As windows authentication is not possible, I need to use sql
server authentication which will send the user name and password in
clear text. I thus don't want to use and powerful account like 'sa' as
it might be sniffed. I am still not clear of any other way to do this
via ado.net on the server EXCEPT...
... I have just learned about SQL-DMO. I think this may solve my
problem as I can take care of linking the servers and creating users,
assigning permissions using this object. It also provides a bunch of
other functionality that I think will be helpful, like being able to
iterate through all the other SQL Servers on the network.
Yes, I agree that ultimately I will need to restrict access to the
database except through stored procedures or views. Currently there is
a legacy application that is hitting each table directly.
Cheers,
Marcus|||Hi Marcus
It is still not clear why this is not in a setup program. If you don't need
to do this functionality more than one then take it out of the main program
and put it into a program where they can use a different account to set it
up. This will be more secure. The login passed to sp_addlinkedsrvlogin does
not have to be the same as the current login.
John
"Marcus" wrote:
> Thanks for your comments, John. The user of the application will need
> to specify what server to link to over TCP/IP (no windows
> authentication possible). Thus, it seems that the application needs to
> call the following 2 stored procedures:
> - sp_addlinkedserverexec
> - sp_addlinkedsrvlogin
> For the procedure "sp_addlinkedsrvlogin" it requires a username and
> password. As windows authentication is not possible, I need to use sql
> server authentication which will send the user name and password in
> clear text. I thus don't want to use and powerful account like 'sa' as
> it might be sniffed. I am still not clear of any other way to do this
> via ado.net on the server EXCEPT...
> ... I have just learned about SQL-DMO. I think this may solve my
> problem as I can take care of linking the servers and creating users,
> assigning permissions using this object. It also provides a bunch of
> other functionality that I think will be helpful, like being able to
> iterate through all the other SQL Servers on the network.
> Yes, I agree that ultimately I will need to restrict access to the
> database except through stored procedures or views. Currently there is
> a legacy application that is hitting each table directly.
> Cheers,
> Marcus
>|||Hi, John. In my scenario, the user is permitted to link up different
servers at runtime. I think I need to rethink my security here. It
would like be better to use something along the lines of your
suggestion. Thanks for you help.
Marcus
Wednesday, March 21, 2012
servers and windows authentication
Hi,
Does anybody know if I can use linked server security
configuration with login's current security context for
windows authenticated sql server logins? I tested and it
doesn't work!!!
I have same windows nt sql server logins on both servers.
Linked server security is configured in a way that all
connections should be made using the login's current
security context (third option). It means that all windows
nt users should be able to refer database objects from the
linked server in their queries (using fully qualified
object name).
Well..., it is not true!
It works ONLY if windows nt user is connected locally to
the server where linked server is defined! However, if the
same windows nt user tries to do that from the client
computer, query fails with login incorrect message.
Did anybody try this? It's so stupid that I cannot believe
it's true.
ThanksHere is the other article I meant to paste in:
http://www.winnetmag.com/SQLServer/Article/ArticleID/23670/23670.html
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks|||I believe that problem is known as the "Double Hop" problem. There is some
information at the bottom of this page the explains:
http://support.microsoft.com/default.aspx?scid=kb;en-us;238477&Product=sql
Also you might consider looking at this one:
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks
Does anybody know if I can use linked server security
configuration with login's current security context for
windows authenticated sql server logins? I tested and it
doesn't work!!!
I have same windows nt sql server logins on both servers.
Linked server security is configured in a way that all
connections should be made using the login's current
security context (third option). It means that all windows
nt users should be able to refer database objects from the
linked server in their queries (using fully qualified
object name).
Well..., it is not true!
It works ONLY if windows nt user is connected locally to
the server where linked server is defined! However, if the
same windows nt user tries to do that from the client
computer, query fails with login incorrect message.
Did anybody try this? It's so stupid that I cannot believe
it's true.
ThanksHere is the other article I meant to paste in:
http://www.winnetmag.com/SQLServer/Article/ArticleID/23670/23670.html
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks|||I believe that problem is known as the "Double Hop" problem. There is some
information at the bottom of this page the explains:
http://support.microsoft.com/default.aspx?scid=kb;en-us;238477&Product=sql
Also you might consider looking at this one:
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks
servers and windows authentication
Hi,
Does anybody know if I can use linked server security
configuration with login's current security context for
windows authenticated sql server logins? I tested and it
doesn't work!!!
I have same windows nt sql server logins on both servers.
Linked server security is configured in a way that all
connections should be made using the login's current
security context (third option). It means that all windows
nt users should be able to refer database objects from the
linked server in their queries (using fully qualified
object name).
Well..., it is not true!
It works ONLY if windows nt user is connected locally to
the server where linked server is defined! However, if the
same windows nt user tries to do that from the client
computer, query fails with login incorrect message.
Did anybody try this? It's so stupid that I cannot believe
it's true.
Thanks
I believe that problem is known as the "Double Hop" problem. There is some
information at the bottom of this page the explains:
http://support.microsoft.com/default...77&Product=sql
Also you might consider looking at this one:
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks
|||I believe that problem is known as the "Double Hop" problem. There is some
information at the bottom of this page the explains:
http://support.microsoft.com/default...77&Product=sql
Also you might consider looking at this one:
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks
|||Here is the other article I meant to paste in:
http://www.winnetmag.com/SQLServer/A...670/23670.html
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks
|||Here is the other article I meant to paste in:
http://www.winnetmag.com/SQLServer/A...670/23670.html
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks
Does anybody know if I can use linked server security
configuration with login's current security context for
windows authenticated sql server logins? I tested and it
doesn't work!!!
I have same windows nt sql server logins on both servers.
Linked server security is configured in a way that all
connections should be made using the login's current
security context (third option). It means that all windows
nt users should be able to refer database objects from the
linked server in their queries (using fully qualified
object name).
Well..., it is not true!
It works ONLY if windows nt user is connected locally to
the server where linked server is defined! However, if the
same windows nt user tries to do that from the client
computer, query fails with login incorrect message.
Did anybody try this? It's so stupid that I cannot believe
it's true.
Thanks
I believe that problem is known as the "Double Hop" problem. There is some
information at the bottom of this page the explains:
http://support.microsoft.com/default...77&Product=sql
Also you might consider looking at this one:
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks
|||I believe that problem is known as the "Double Hop" problem. There is some
information at the bottom of this page the explains:
http://support.microsoft.com/default...77&Product=sql
Also you might consider looking at this one:
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks
|||Here is the other article I meant to paste in:
http://www.winnetmag.com/SQLServer/A...670/23670.html
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks
|||Here is the other article I meant to paste in:
http://www.winnetmag.com/SQLServer/A...670/23670.html
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks
servers and windows authentication
Hi,
Does anybody know if I can use linked server security
configuration with login's current security context for
windows authenticated sql server logins? I tested and it
doesn't work!!!
I have same windows nt sql server logins on both servers.
Linked server security is configured in a way that all
connections should be made using the login's current
security context (third option). It means that all windows
nt users should be able to refer database objects from the
linked server in their queries (using fully qualified
object name).
Well..., it is not true!
It works ONLY if windows nt user is connected locally to
the server where linked server is defined! However, if the
same windows nt user tries to do that from the client
computer, query fails with login incorrect message.
Did anybody try this? It's so stupid that I cannot believe
it's true.
ThanksI believe that problem is known as the "Double Hop" problem. There is some
information at the bottom of this page the explains:
http://support.microsoft.com/defaul...477&Product=sql
Also you might consider looking at this one:
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx
.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks|||Here is the other article I meant to paste in:
http://www.winnetmag.com/SQLServer/...3670/23670.html
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx
.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thankssql
Does anybody know if I can use linked server security
configuration with login's current security context for
windows authenticated sql server logins? I tested and it
doesn't work!!!
I have same windows nt sql server logins on both servers.
Linked server security is configured in a way that all
connections should be made using the login's current
security context (third option). It means that all windows
nt users should be able to refer database objects from the
linked server in their queries (using fully qualified
object name).
Well..., it is not true!
It works ONLY if windows nt user is connected locally to
the server where linked server is defined! However, if the
same windows nt user tries to do that from the client
computer, query fails with login incorrect message.
Did anybody try this? It's so stupid that I cannot believe
it's true.
ThanksI believe that problem is known as the "Double Hop" problem. There is some
information at the bottom of this page the explains:
http://support.microsoft.com/defaul...477&Product=sql
Also you might consider looking at this one:
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx
.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thanks|||Here is the other article I meant to paste in:
http://www.winnetmag.com/SQLServer/...3670/23670.html
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:228da01c45d51$a58a3990$a401280a@.phx
.gbl...
> Hi,
> Does anybody know if I can use linked server security
> configuration with login's current security context for
> windows authenticated sql server logins? I tested and it
> doesn't work!!!
> I have same windows nt sql server logins on both servers.
> Linked server security is configured in a way that all
> connections should be made using the login's current
> security context (third option). It means that all windows
> nt users should be able to refer database objects from the
> linked server in their queries (using fully qualified
> object name).
> Well..., it is not true!
> It works ONLY if windows nt user is connected locally to
> the server where linked server is defined! However, if the
> same windows nt user tries to do that from the client
> computer, query fails with login incorrect message.
> Did anybody try this? It's so stupid that I cannot believe
> it's true.
> Thankssql
Monday, March 12, 2012
servers
hello,
is it possible to use windows authentication to link two sql servers? or is it possible only with a valid sql server login?
thanks!
You can use the following to create a linked server definition that will
allow Windows Authenticated users to logon to linked server.
exec sp_addlinkedserver N'TEST2'
sp_addlinkedsrvlogin 'test2',
@.useself = 'true'
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:E05D0DC8-597A-4986-94C3-50BCBC75228D@.microsoft.com...
> hello,
> is it possible to use windows authentication to link two sql servers? or
is it possible only with a valid sql server login?
> thanks!
>
is it possible to use windows authentication to link two sql servers? or is it possible only with a valid sql server login?
thanks!
You can use the following to create a linked server definition that will
allow Windows Authenticated users to logon to linked server.
exec sp_addlinkedserver N'TEST2'
sp_addlinkedsrvlogin 'test2',
@.useself = 'true'
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:E05D0DC8-597A-4986-94C3-50BCBC75228D@.microsoft.com...
> hello,
> is it possible to use windows authentication to link two sql servers? or
is it possible only with a valid sql server login?
> thanks!
>
servers
hello,
is it possible to use windows authentication to link two sql servers? or is
it possible only with a valid sql server login?
thanks!You can use the following to create a linked server definition that will
allow Windows Authenticated users to logon to linked server.
exec sp_addlinkedserver N'TEST2'
sp_addlinkedsrvlogin 'test2',
@.useself = 'true'
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:E05D0DC8-597A-4986-94C3-50BCBC75228D@.microsoft.com...
> hello,
> is it possible to use windows authentication to link two sql servers? or
is it possible only with a valid sql server login?
> thanks!
>
is it possible to use windows authentication to link two sql servers? or is
it possible only with a valid sql server login?
thanks!You can use the following to create a linked server definition that will
allow Windows Authenticated users to logon to linked server.
exec sp_addlinkedserver N'TEST2'
sp_addlinkedsrvlogin 'test2',
@.useself = 'true'
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:E05D0DC8-597A-4986-94C3-50BCBC75228D@.microsoft.com...
> hello,
> is it possible to use windows authentication to link two sql servers? or
is it possible only with a valid sql server login?
> thanks!
>
Friday, March 9, 2012
server with windows authentication and wihout AD(kerberos)
Hi All!
I have two Windows Server 2000 with SQL Servers 2000 installed and
uses windows authentication only.
Can i use linked server without Security Account Delegation (no domain, AD,
... ) and not use SQL authentication?
P.S.: Presently I use pass-through windows account authentication (same
username/password on two windows servers), but this not works with linked
server.
Thanks in advance.
Igor.Hi
Mike answered this in microsoft.public.sqlserver.securitytools. Please do
not multi-post.
John
"Igor Aflatunov" <Igor Aflatunov@.discussions.microsoft.com> wrote in message
news:CA9B20F2-C6FA-4F59-83F3-7A809DC09184@.microsoft.com...
> Hi All!
> I have two Windows Server 2000 with SQL Servers 2000 installed and
> uses windows authentication only.
> Can i use linked server without Security Account Delegation (no domain,
> AD,
> ... ) and not use SQL authentication?
> P.S.: Presently I use pass-through windows account authentication (same
> username/password on two windows servers), but this not works with linked
> server.
> Thanks in advance.
> Igor.
>
I have two Windows Server 2000 with SQL Servers 2000 installed and
uses windows authentication only.
Can i use linked server without Security Account Delegation (no domain, AD,
... ) and not use SQL authentication?
P.S.: Presently I use pass-through windows account authentication (same
username/password on two windows servers), but this not works with linked
server.
Thanks in advance.
Igor.Hi
Mike answered this in microsoft.public.sqlserver.securitytools. Please do
not multi-post.
John
"Igor Aflatunov" <Igor Aflatunov@.discussions.microsoft.com> wrote in message
news:CA9B20F2-C6FA-4F59-83F3-7A809DC09184@.microsoft.com...
> Hi All!
> I have two Windows Server 2000 with SQL Servers 2000 installed and
> uses windows authentication only.
> Can i use linked server without Security Account Delegation (no domain,
> AD,
> ... ) and not use SQL authentication?
> P.S.: Presently I use pass-through windows account authentication (same
> username/password on two windows servers), but this not works with linked
> server.
> Thanks in advance.
> Igor.
>
Wednesday, March 7, 2012
server using Windows authentication
Hi,
I´m having problems with using Windows authentication with a linked server.
When connecting to the linked server I receive the error:
Error 18452: Login failed for user 'domain/name of a local windows account
on the linked server'. Reason: Not associated with a trusted SQL Server
connection.
When I change to mixed mode on the linked server everything works fine, but
this is not an option that I want to use. How do I make this work with
Windows authentication?
Best regards,
WencheThis is a security delegation issue. Please take a look at this article for
more info.
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_2gmm.asp?frame=true
-oj
Rac v2.2 & QALite!
http://www.rac4sql.net
"Wenche" <weels@.dne.com> wrote in message
news:uiBMiHVkDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I´m having problems with using Windows authentication with a linked
server.
> When connecting to the linked server I receive the error:
> Error 18452: Login failed for user 'domain/name of a local windows account
> on the linked server'. Reason: Not associated with a trusted SQL Server
> connection.
> When I change to mixed mode on the linked server everything works fine,
but
> this is not an option that I want to use. How do I make this work with
> Windows authentication?
> Best regards,
> Wenche
>
I´m having problems with using Windows authentication with a linked server.
When connecting to the linked server I receive the error:
Error 18452: Login failed for user 'domain/name of a local windows account
on the linked server'. Reason: Not associated with a trusted SQL Server
connection.
When I change to mixed mode on the linked server everything works fine, but
this is not an option that I want to use. How do I make this work with
Windows authentication?
Best regards,
WencheThis is a security delegation issue. Please take a look at this article for
more info.
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_2gmm.asp?frame=true
-oj
Rac v2.2 & QALite!
http://www.rac4sql.net
"Wenche" <weels@.dne.com> wrote in message
news:uiBMiHVkDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I´m having problems with using Windows authentication with a linked
server.
> When connecting to the linked server I receive the error:
> Error 18452: Login failed for user 'domain/name of a local windows account
> on the linked server'. Reason: Not associated with a trusted SQL Server
> connection.
> When I change to mixed mode on the linked server everything works fine,
but
> this is not an option that I want to use. How do I make this work with
> Windows authentication?
> Best regards,
> Wenche
>
server using NT authentication
Hi,
I have successfully set up linked servers using SQL authentication. However,
I can't figure out how to configure a linked server using NT authentication.
I would appreciate it if anyone could help with this matter.
Sincerely,
Shan
Think you just want the users crrent security context on the security tab.
"Shan" wrote:
> Hi,
> I have successfully set up linked servers using SQL authentication. However,
> I can't figure out how to configure a linked server using NT authentication.
> I would appreciate it if anyone could help with this matter.
> Sincerely,
> Shan
>
I have successfully set up linked servers using SQL authentication. However,
I can't figure out how to configure a linked server using NT authentication.
I would appreciate it if anyone could help with this matter.
Sincerely,
Shan
Think you just want the users crrent security context on the security tab.
"Shan" wrote:
> Hi,
> I have successfully set up linked servers using SQL authentication. However,
> I can't figure out how to configure a linked server using NT authentication.
> I would appreciate it if anyone could help with this matter.
> Sincerely,
> Shan
>
Subscribe to:
Posts (Atom)