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

servers and permissions

Please forgive my ignorance - I'm new at this...
I have a user who needs permissions to alter properties on linked servers.
The only way I can see to do this is to grant this user's login the sysadmin
role. I really don't want to do this, as the user should not have that leve
l
of permissions on some of the local databases.
Is there a way to grant the user admin permissions on the linked servers but
not using sysadmin?
Thanks!Hi Cyd,
It depends on what they need to modify and what version of
SQL Server we're talking about.
I'm going to guess it's SQL 2000. I haven't tried this but
have you tried adding the user to the serveradmin server
role and giving permissions to sysservers in the master
database? Some of the actions when changing linked server
properties in Enterprise Manager just execute sp_configure
to allow updates to system tables and then the actions
update the sysservers table.
-Sue
On Mon, 30 Oct 2006 10:44:02 -0800, Cyd
<Cyd@.discussions.microsoft.com> wrote:

>Please forgive my ignorance - I'm new at this...
>I have a user who needs permissions to alter properties on linked servers.
>The only way I can see to do this is to grant this user's login the sysadmi
n
>role. I really don't want to do this, as the user should not have that lev
el
>of permissions on some of the local databases.
>Is there a way to grant the user admin permissions on the linked servers bu
t
>not using sysadmin?
>Thanks!|||I should have specified - it's SQL Server 2005 - my apologies...
"Sue Hoegemeier" wrote:

> Hi Cyd,
> It depends on what they need to modify and what version of
> SQL Server we're talking about.
> I'm going to guess it's SQL 2000. I haven't tried this but
> have you tried adding the user to the serveradmin server
> role and giving permissions to sysservers in the master
> database? Some of the actions when changing linked server
> properties in Enterprise Manager just execute sp_configure
> to allow updates to system tables and then the actions
> update the sysservers table.
> -Sue
> On Mon, 30 Oct 2006 10:44:02 -0800, Cyd
> <Cyd@.discussions.microsoft.com> wrote:
>
>|||You would want to look at ALTER ANY LINKED SERVER
permissions for configuration changes and ALTER ANY LOGIN
for adding or dropping security mappings.
-Sue
On Tue, 31 Oct 2006 12:01:01 -0800, Cyd
<Cyd@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I should have specified - it's SQL Server 2005 - my apologies...
>"Sue Hoegemeier" wrote:
>

server, join, same query, different plans for different use

We recently moved a database from one SQL server to another and replicated
logins, users, permissions, etc. The problem occurs when joins are run from
the original server, referencing tables on the new server, using the
four-part name. Using the same query, some users time out with an execution
plan that looks like it ignores the indexes on the remote server. Other's
execute in seconds.
I've been over permissions with a fine-tooth comb. The users who succeed are
domain or SQL admins. Those who fail are members of a domain group, specified
as a login and database user with select priviledges on all tables and views.
Ownership is sa, just as it is for databases on the original server. Can
anyone suggest what else I might look for? Thanks!
Thanks,
CGW
The permissions across the link are based on the logins associated with the
link. You may have set them up with user accounts on the remote server, but
they credentials passed to that remote server are those configured for the
link. My guess is that you need to set up for users to make connections
"using the login's current security context". In EM you can check under the
Security tab on your linked server properties or run sp_helplinkedsrvlogin in
QA to see what you get.
HTH,
John Scragg
"CGW" wrote:

> We recently moved a database from one SQL server to another and replicated
> logins, users, permissions, etc. The problem occurs when joins are run from
> the original server, referencing tables on the new server, using the
> four-part name. Using the same query, some users time out with an execution
> plan that looks like it ignores the indexes on the remote server. Other's
> execute in seconds.
> I've been over permissions with a fine-tooth comb. The users who succeed are
> domain or SQL admins. Those who fail are members of a domain group, specified
> as a login and database user with select priviledges on all tables and views.
> Ownership is sa, just as it is for databases on the original server. Can
> anyone suggest what else I might look for? Thanks!
> --
> Thanks,
> CGW

server, join, same query, different plans for different use

We recently moved a database from one SQL server to another and replicated
logins, users, permissions, etc. The problem occurs when joins are run from
the original server, referencing tables on the new server, using the
four-part name. Using the same query, some users time out with an execution
plan that looks like it ignores the indexes on the remote server. Other's
execute in seconds.
I've been over permissions with a fine-tooth comb. The users who succeed are
domain or SQL admins. Those who fail are members of a domain group, specified
as a login and database user with select priviledges on all tables and views.
Ownership is sa, just as it is for databases on the original server. Can
anyone suggest what else I might look for? Thanks!
--
Thanks,
CGWThe permissions across the link are based on the logins associated with the
link. You may have set them up with user accounts on the remote server, but
they credentials passed to that remote server are those configured for the
link. My guess is that you need to set up for users to make connections
"using the login's current security context". In EM you can check under the
Security tab on your linked server properties or run sp_helplinkedsrvlogin in
QA to see what you get.
HTH,
John Scragg
"CGW" wrote:
> We recently moved a database from one SQL server to another and replicated
> logins, users, permissions, etc. The problem occurs when joins are run from
> the original server, referencing tables on the new server, using the
> four-part name. Using the same query, some users time out with an execution
> plan that looks like it ignores the indexes on the remote server. Other's
> execute in seconds.
> I've been over permissions with a fine-tooth comb. The users who succeed are
> domain or SQL admins. Those who fail are members of a domain group, specified
> as a login and database user with select priviledges on all tables and views.
> Ownership is sa, just as it is for databases on the original server. Can
> anyone suggest what else I might look for? Thanks!
> --
> Thanks,
> CGW|||Good guess, but actually, that is how they are set up. What else do you think
might be the problem?
--
Thanks,
CGW
"John Scragg" wrote:
> The permissions across the link are based on the logins associated with the
> link. You may have set them up with user accounts on the remote server, but
> they credentials passed to that remote server are those configured for the
> link. My guess is that you need to set up for users to make connections
> "using the login's current security context". In EM you can check under the
> Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> QA to see what you get.
> HTH,
> John Scragg
> "CGW" wrote:
> > We recently moved a database from one SQL server to another and replicated
> > logins, users, permissions, etc. The problem occurs when joins are run from
> > the original server, referencing tables on the new server, using the
> > four-part name. Using the same query, some users time out with an execution
> > plan that looks like it ignores the indexes on the remote server. Other's
> > execute in seconds.
> >
> > I've been over permissions with a fine-tooth comb. The users who succeed are
> > domain or SQL admins. Those who fail are members of a domain group, specified
> > as a login and database user with select priviledges on all tables and views.
> >
> > Ownership is sa, just as it is for databases on the original server. Can
> > anyone suggest what else I might look for? Thanks!
> > --
> > Thanks,
> >
> > CGW|||Dunno. Maybe the ownership chain is broken. If the admin roles work, it may
be the case that the chain is broken for regular users.
Using Ownership Chaining (http://tinyurl.com/8q5dq)
Cross DB Ownership Chaining (http://tinyurl.com/7n9jf)
Best of luck,
John
"CGW" wrote:
> Good guess, but actually, that is how they are set up. What else do you think
> might be the problem?
> --
> Thanks,
> CGW
>
> "John Scragg" wrote:
> > The permissions across the link are based on the logins associated with the
> > link. You may have set them up with user accounts on the remote server, but
> > they credentials passed to that remote server are those configured for the
> > link. My guess is that you need to set up for users to make connections
> > "using the login's current security context". In EM you can check under the
> > Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> > QA to see what you get.
> >
> > HTH,
> >
> > John Scragg
> >
> > "CGW" wrote:
> >
> > > We recently moved a database from one SQL server to another and replicated
> > > logins, users, permissions, etc. The problem occurs when joins are run from
> > > the original server, referencing tables on the new server, using the
> > > four-part name. Using the same query, some users time out with an execution
> > > plan that looks like it ignores the indexes on the remote server. Other's
> > > execute in seconds.
> > >
> > > I've been over permissions with a fine-tooth comb. The users who succeed are
> > > domain or SQL admins. Those who fail are members of a domain group, specified
> > > as a login and database user with select priviledges on all tables and views.
> > >
> > > Ownership is sa, just as it is for databases on the original server. Can
> > > anyone suggest what else I might look for? Thanks!
> > > --
> > > Thanks,
> > >
> > > CGW|||Thanks, we're still researching. When we link with a specific security
context, we can make the problem go away, which gave us some clues.
--
Thanks,
CGW
"John Scragg" wrote:
> Dunno. Maybe the ownership chain is broken. If the admin roles work, it may
> be the case that the chain is broken for regular users.
> Using Ownership Chaining (http://tinyurl.com/8q5dq)
> Cross DB Ownership Chaining (http://tinyurl.com/7n9jf)
> Best of luck,
> John
> "CGW" wrote:
> > Good guess, but actually, that is how they are set up. What else do you think
> > might be the problem?
> > --
> > Thanks,
> >
> > CGW
> >
> >
> > "John Scragg" wrote:
> >
> > > The permissions across the link are based on the logins associated with the
> > > link. You may have set them up with user accounts on the remote server, but
> > > they credentials passed to that remote server are those configured for the
> > > link. My guess is that you need to set up for users to make connections
> > > "using the login's current security context". In EM you can check under the
> > > Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> > > QA to see what you get.
> > >
> > > HTH,
> > >
> > > John Scragg
> > >
> > > "CGW" wrote:
> > >
> > > > We recently moved a database from one SQL server to another and replicated
> > > > logins, users, permissions, etc. The problem occurs when joins are run from
> > > > the original server, referencing tables on the new server, using the
> > > > four-part name. Using the same query, some users time out with an execution
> > > > plan that looks like it ignores the indexes on the remote server. Other's
> > > > execute in seconds.
> > > >
> > > > I've been over permissions with a fine-tooth comb. The users who succeed are
> > > > domain or SQL admins. Those who fail are members of a domain group, specified
> > > > as a login and database user with select priviledges on all tables and views.
> > > >
> > > > Ownership is sa, just as it is for databases on the original server. Can
> > > > anyone suggest what else I might look for? Thanks!
> > > > --
> > > > Thanks,
> > > >
> > > > CGW

server, join, same query, different plans for different use

We recently moved a database from one SQL server to another and replicated
logins, users, permissions, etc. The problem occurs when joins are run from
the original server, referencing tables on the new server, using the
four-part name. Using the same query, some users time out with an execution
plan that looks like it ignores the indexes on the remote server. Other's
execute in seconds.
I've been over permissions with a fine-tooth comb. The users who succeed are
domain or SQL admins. Those who fail are members of a domain group, specifie
d
as a login and database user with select priviledges on all tables and views
.
Ownership is sa, just as it is for databases on the original server. Can
anyone suggest what else I might look for? Thanks!
--
Thanks,
CGWThe permissions across the link are based on the logins associated with the
link. You may have set them up with user accounts on the remote server, but
they credentials passed to that remote server are those configured for the
link. My guess is that you need to set up for users to make connections
"using the login's current security context". In EM you can check under the
Security tab on your linked server properties or run sp_helplinkedsrvlogin i
n
QA to see what you get.
HTH,
John Scragg
"CGW" wrote:

> We recently moved a database from one SQL server to another and replicated
> logins, users, permissions, etc. The problem occurs when joins are run fro
m
> the original server, referencing tables on the new server, using the
> four-part name. Using the same query, some users time out with an executio
n
> plan that looks like it ignores the indexes on the remote server. Other's
> execute in seconds.
> I've been over permissions with a fine-tooth comb. The users who succeed a
re
> domain or SQL admins. Those who fail are members of a domain group, specif
ied
> as a login and database user with select priviledges on all tables and vie
ws.
> Ownership is sa, just as it is for databases on the original server. Can
> anyone suggest what else I might look for? Thanks!
> --
> Thanks,
> CGW

server, Excel - Permissions Issue

Hello,
Linked Server to Excel spreadsheet
I have no problem executing a Linked Server query through Query Analyzer
(Administror) or through an ASP page connecting through a Login setup throug
h
Enterprise Manager with: Server Role - Systems Administrator. However any
ohter lesser Login / Role yields:
ERROR -
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'
NOTES:
* Link Server Properties / Security is - DEFAULT
* Permissions on Excel spreadsheet & directory are - Everyone
Thus, I believe what I'm looking for are the What and How on setting
permissons for executing this query on the linked server?
I have been struggling with this for several days and do not find much on
the web per this issue... any help would be appreciated.
Thanks, j"JLatiolait" <JLatiolait@.discussions.microsoft.com> wrote in message
news:1F47F9C9-18EF-4B60-9D36-42BBD1C56052@.microsoft.com...

> Linked Server to Excel spreadsheet
> I have no problem executing a Linked Server query through Query Analyzer
> (Administror) or through an ASP page connecting through a Login setup
through
> Enterprise Manager with: Server Role - Systems Administrator. However any
> ohter lesser Login / Role yields:
> ERROR -
> Microsoft OLE DB Provider for SQL Server (0x80040E14)
> Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'
> NOTES:
> * Link Server Properties / Security is - DEFAULT
> * Permissions on Excel spreadsheet & directory are - Everyone
Everyone = what permissions at the NTFS layer?
Steve