July 30, 2011 at 10:09 am
I posted this question Friday morning, but it disappeared again a few hours later - don't know what happened. Sorry for the repeat if someone responded to it before it vanished.
I'm running and developing on a server in an outfit formerly using just Windows workgroups, but the IT department recently decided to switch the entire place over to a domain, mostly trying to cut down on people using illegal software. They're being pretty cautious about it, and when I asked them not to switch the department for which I'm running this server until I was sure about how a SQL Server db on it will function in a domain, they were cooperative. At my request, they moved just the server into the domain, left the original workgroup accounts on it and added some domain accounts. My dev machine and all people in the department are still in workgroups, and I have a virtual machine set up on one of the IT guys' desktops for experiments, with a domain account to for me on it. I still have an account with administrator privileges on the server.
Most of the stuff is still fine, but one of my stored procedures, quoted below, has quit working properly. If I comment out the line
"WITH EXECUTE AS OWNER"
it works when I am logged on to the machine via remote desktop and into SQL Server SSMSE as sa. But it doesn't work from other machines - I get an error message thet Execute permission is denied on object xp_logininfo, database 'mssqlsystemresource', schema 'sys'. (#229)
When I remove the comment and let the definition run again, including "WITH EXECUTE AS OWNER", I no longer get the permission error, but I also don't get any results. The procedure executes without complaint, but returns nothing, even when running as logged-in sa.
What has changed by putting the server in a domain and what must I do to get this procedure to execute properly?
Pete
USE [PaleoDataProPokusy]
CREATE PROCEDURE [dbo].[spClenSkupiny] @Skupina as varchar(100)
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
declare @Server_Skupina varchar(100)
set @Server_Skupina = 'PALEO-SERVER\' + @Skupina
print @server_skupina
EXEC master.dbo.xp_logininfo @Server_Skupina, 'members'
END
August 1, 2011 at 10:09 am
From the xp_logininfo documentation here http://technet.microsoft.com/en-us/library/ms190369.aspx
If account_name and members are specified, a list of the next-level members of the group is returned. If account_name is a local group, the listing can include local users, domain users, and groups. If account_name is a domain account, the list is made up of domain users. SQL Server must connect to the domain controller to retrieve group membership information. If the server cannot contact the domain controller, no information will be returned.
I am wondering if your SQL Server is able to connect to Active Directory.
Some random thoughts:
1. Who is the dbo of your database?
SELECT name,
SUSER_SNAME(owner_sid) AS dbo
FROM sys.databases
ORDER BY name ;
A generally accepted good practice is to change the owner to sa unless you have a specific security implementation that would make that a bad idea.
2. Check the Services applet to see which account the SQL Server service is running as. If it's a local account then SQL Server may not have access to Active Directory.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2011 at 3:56 pm
opc.three (8/1/2011)
From the xp_logininfo documentation here http://technet.microsoft.com/en-us/library/ms190369.aspxIf account_name and members are specified, a list of the next-level members of the group is returned. If account_name is a local group, the listing can include local users, domain users, and groups. If account_name is a domain account, the list is made up of domain users. SQL Server must connect to the domain controller to retrieve group membership information. If the server cannot contact the domain controller, no information will be returned.
I am wondering if your SQL Server is able to connect to Active Directory.
I don't know - how do I tell? I do get some domain stuff in the listing, when it works, along with all the local accounts. I've pasted it in at the bottom of this post. The three top rows are from the domain (named NMP). I asked the domain guy to create the same groups I have on the local machine in the domain for me, so I could convert this app to work in a domain. I don't see all three of them, only the last NMP line is one of the groups. The groups are only for my purposes, to check in the app whether a person is allowed to do something - they have no differing machine privileges between groups, and none of them have admin privilege.
1. Who is the dbo of your database?
SELECT name,SUSER_SNAME(owner_sid) AS dbo
FROM sys.databases
ORDER BY name ;
All but one were sa, I changed it to sa with thisexec sp_changedbowner 'sa'but the results are the same. When I comment out the 'WITH EXECUTE AS OWNER' clause, it works for sa and everyone else gets a permission error. When that line is uncommented, no error, but no resultset, not even for logged-in sa.
A generally accepted good practice is to change the owner to sa unless you have a specific security implementation that would make that a bad idea.
One happened to be owned by my personal account, which has machine admin privileges, because I was using SSMSE from my local machine when I created that db. All others were sa, because I created them directly from the server, logged in as sa. I'm still not quite clear on the concept of a database owner, or why it matters. It seems that setting permissions to allow appropriate access should handle anything, but I'm reading everything I can get my hands on, maybe it'll become clearer eventually.
2. Check the Services applet to see which account the SQL Server service is running as. If it's a local account then SQL Server may not have access to Active Directory.
I did know that could happen. I thought that since SQL Server runs as a service, it doesn't run under any particular account. How do I check this? In the services dialog, it shows SQL Server (SQLSEXPRESSW2008) running under Network Service - does that help?
Resultset from xp_Logininfo, logged in as sa, running the stored procedure without the 'WITH EXECUTE AS OWNER' clause.
NMP\AdministratoruseruserNMP\AdministratorPALEO-SERVER\PaleoUzivatel
NMP\danespeuseruserNMP\danespePALEO-SERVER\PaleoUzivatel
NMP\PaleoUzivatelgroupuserNMP\PaleoUzivatelPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\BorekuseruserPALEO-SERVER\BorekPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\danesuseruserPALEO-SERVER\danesPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\EkrtuseruserPALEO-SERVER\EkrtPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\chorviuseruserPALEO-SERVER\chorviPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\JanuseruserPALEO-SERVER\JanPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\Jan SklenáruseruserPALEO-SERVER\Jan SklenárPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\JanauseruserPALEO-SERVER\JanaPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\Jirí KvacekuseruserPALEO-SERVER\Jirí KvacekPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\KamiluseruserPALEO-SERVER\KamilPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\KarolinauseruserPALEO-SERVER\KarolinaPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\LajblovauseruserPALEO-SERVER\LajblovaPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\MartinuseruserPALEO-SERVER\MartinPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\Martin ValentuseruserPALEO-SERVER\Martin ValentPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\MedveduseruserPALEO-SERVER\MedvedPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\MilanuseruserPALEO-SERVER\MilanPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\Milan LibertínuseruserPALEO-SERVER\Milan LibertínPALEO-SERVER\PaleoUzivatel
PALEO-SERVERmuseruserPALEO-SERVERmPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\paleouseruserPALEO-SERVER\paleoPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\TurekuseruserPALEO-SERVER\TurekPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\VojtauseruserPALEO-SERVER\VojtaPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\ZagorsekuseruserPALEO-SERVER\ZagorsekPALEO-SERVER\PaleoUzivatel
PALEO-SERVER\ZuzkauseruserPALEO-SERVER\ZuzkaPALEO-SERVER\PaleoUzivatel
August 2, 2011 at 4:56 pm
The service running as Network Service sounds fine. You are obviously receiving domain info per the xp_logininfo results you posted.
One more request please.
Most of the stuff is still fine, but one of my stored procedures, quoted below, has quit working properly. If I comment out the line
"WITH EXECUTE AS OWNER"
Who owns the stored procedure?
USE db ;
GO
SELECT USER_NAME(OBJECTPROPERTY(OBJECT_ID(N'schema_name.procedure_name'), 'ownerid')) AS 'proc_owner' ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2011 at 5:03 pm
opc.three (8/2/2011)
The service running as Network Service sounds fine. You are obviously receiving domain info per the xp_logininfo results you posted.
Great, that's a step in the right direction.
Who owns the stored procedure?
USE db ;GO
SELECT USER_NAME(OBJECTPROPERTY(OBJECT_ID(N'schema_name.procedure_name'), 'ownerid')) AS 'proc_owner' ;
I edited it to read like thisUSE PaleoDataProPokusy
GO
SELECT USER_NAME(OBJECTPROPERTY(OBJECT_ID(N'dbo.[spClenSkupiny]'), 'ownerid')) AS 'proc_owner'
and got this one line: dbo
Is that right?
August 2, 2011 at 9:48 pm
From xp_logininfo Books Online article
Permissions
Requires membership in the sysadmin fixed server role or membership in the public fixed database role in the master database with EXECUTE permission granted.
I think this means you will need to change the owner of the procedure from dbo to a Database User that maps to a Server Login that is in the sysadmin Fixed Server Role. I think...I don't the means to test it at the moment. If you get to it before I do please post back. If I don't hear back I'll test it tomorrow and will let you know.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 3, 2011 at 12:04 am
opc.three (8/2/2011)
From xp_logininfo Books Online articlePermissions
Requires membership in the sysadmin fixed server role or membership in the public fixed database role in the master database with EXECUTE permission granted.
I think this means you will need to change the owner of the procedure from dbo to a Database User that maps to a Server Login that is in the sysadmin Fixed Server Role. I think...I don't the means to test it at the moment. If you get to it before I do please post back. If I don't hear back I'll test it tomorrow and will let you know.
You've lost me. The database owner is sa. If the procedure owner is dbo, isn't that also sa?
And since the database owner is sa, why does the 'WITH EXECUTE AS OWNER' clause make any difference at all in how it runs? Seems to me it should run the same with or without that clause when I'm logged in as sa. It doesn't, though, so obviously I don't understand something about it.
August 3, 2011 at 5:53 am
pdanes2 (8/3/2011)
opc.three (8/2/2011)
From xp_logininfo Books Online articlePermissions
Requires membership in the sysadmin fixed server role or membership in the public fixed database role in the master database with EXECUTE permission granted.
I think this means you will need to change the owner of the procedure from dbo to a Database User that maps to a Server Login that is in the sysadmin Fixed Server Role. I think...I don't the means to test it at the moment. If you get to it before I do please post back. If I don't hear back I'll test it tomorrow and will let you know.
You've lost me. The database owner is sa. If the procedure owner is dbo, isn't that also sa?
That's the hope, however I have a feeling it will not work that way. That's why I said "I think" before. This proc will likely require you to switch from using the WITH EXECUTE AS clause to using EXECUTE AS LOGIN = 'login_name' and REVERT inline within your proc definition. When using the WITH EXECUTE AS clause in your proc I think you're stuck in the context of a database-level principal, when in this case you need to be the context of a server-level principal, namely sa or some other sysadmin. I think you might need to change your proc to something like this:
CREATE PROC dbo.test_xp_logininfo
AS
BEGIN
EXECUTE AS LOGIN = 'some_sysadmin_login_name' ;
EXEC sys.xp_logininfo ;
REVERT ;
END
Using EXECUTE AS LOGIN brings with it a slew of issues regarding impersonation. The technique can be looked up in Books Online. There are examples and explanations of what needs to be done to make it work. You may also want to look into signing your proc with a certificate as an alternative to using impersonation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 3, 2011 at 11:50 am
opc.three (8/3/2011)
From xp_logininfo Books Online articleYou've lost me. The database owner is sa. If the procedure owner is dbo, isn't that also sa?
That's the hope, however I have a feeling it will not work that way. That's why I said "I think" before. This proc will likely require you to switch from using the WITH EXECUTE AS clause to using EXECUTE AS LOGIN = 'login_name' and REVERT inline within your proc definition. When using the WITH EXECUTE AS clause in your proc I think you're stuck in the context of a database-level principal, when in this case you need to be the context of a server-level principal, namely sa or some other sysadmin. I think you might need to change your proc to something like this:
CREATE PROC dbo.test_xp_logininfoAS
BEGIN
EXECUTE AS LOGIN = 'some_sysadmin_login_name' ;
EXEC sys.xp_logininfo ;
REVERT ;
END
All right, I tried it, this way:
CREATE PROCEDURE [dbo].[spClenSkupiny]
@Skupina as varchar(100)
--WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
declare @Server_Skupina varchar(100)
set @Server_Skupina = 'PALEO-SERVER\' + @Skupina
print @server_skupina
EXECUTE AS LOGIN = 'sa'
EXEC master.dbo.xp_logininfo @Server_Skupina, 'members'
REVERT
ENDDifferent results, but still not right:
1. Logged into server as sa, 'WITH EXECUTE AS OWNER' comented out, works fine.
2. Run from my app via ODBC, different error than before: Cannot execute as server principal the principal "sa" does not exist, this type of principal cannot be impersonated, or you do not have persission. (#15406)
3. Logged into server as sa, 'WITH EXECUTE AS OWNER' uncomented, same error message as #2.
4. Run from my app via ODBC, 'WITH EXECUTE AS OWNER' uncomented, same error message as #2.
I have other logins in the instance, but they are all groups. Nobody else does anything with this server but me (except the D:\ drive has some shared storage folders, mapped to drive letters on users' machines), so there are no individual logins. I do most of what I need to via SSMSE from my desktop, logged on with Windows security. The app connects via ODBC. The group in which my desktop is in has db_owner in its list of role memberships. When I need to do something on the server for which my local machine permissions are inadequate, I connect directly to the server via Remote Desktop, log onto SSMSE on the server as sa and do what I need.
I've been in the public role through this group, just now I added the sysadmin role to the group login, no difference, the procedure still bombs from my machine.
Using EXECUTE AS LOGIN brings with it a slew of issues regarding impersonation. The technique can be looked up in Books Online. There are examples and explanations of what needs to be done to make it work.
I'm looking at that now, but not making much sense of it. For instance: 'In effect, the statement allows the caller to simulate the act of logging in as that impersonated login.' seems to imply that the EXECUTE AS 'sa' is equivalent to logging in as sa, but clearly, that's not what's happening.
You may also want to look into signing your proc with a certificate as an alternative to using impersonation.
I did that for another sp, that was using this:
exec master.dbo.xp_cmdshell @PrilohyShellCMDbut when I started looking at reasons why the xp_logininfo command wasn't working, that didn't come up, only the 'EXECUTE AS...' stuff, so I thought that the two required different massaging to make them work. But I'll try that here as well. I don't understand the certificate business very well either, but I did manage to make it work.
I'm beginning to wonder if I'm not suffering from tunnel vision, though, which does happen to me a lot. Might there be a better way altogether of doing this, that is, finding out who is using the app and what group they belong to in the domain? My ultimate goal is for the app to find out who is running it, which group they belong to in the domain and enable or disable certain controls based on that info. It was working fine the way I had it, but maybe the shift to domains requires a new way of doing this.
August 3, 2011 at 12:14 pm
All right, as we used to say in the Marine Corps, 'Belay that last'. The posts in my most recent test were conducted from my dev machine, but just now I tried it from a virtual test machine running on the domain admin's desktop, and that does get some results. Not sure just what yet, it's not exactly what I was expecting, but at least it doesn't bomb.
The virtual machine is in the domain, where my dev machine is not. I wanted to do this slowly, to avoid exactly theses kind of hassles, but maybe it's a mistake. I may just need to have everything converted to the domain, sink or swim, and simply deal with problems as they arise. This half-way approach seems to be causing problems all its own, and I'm spending time dealing with them, instead of working on real stuff.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply