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
