I have created a database fronted by an ASP.Net application. It's all nice and simple, and I only need a very simple level of security (and even that is only as a protection against accidents rather than maliciousness). My intention is that users connect using Windows impersonation (<identity="true">), with the database creator having full access and the public group (I'm talking SQL groups here) having specific premissions granted on specific tables.
If I set <identity="false"> on my XP box the application connects to the database as [MACHINE\ASPNET]. This is easy to set up access for - I simply do a
CREATE LOGIN [MACHINE\ASPNET] FROM WINDOWS
and then within the actual database do a
CREATE USER [MACHINE\ASPNET]
But as I said, I want to use Windows impersonation. When I set <identity="true">, the application correctly attempts to connect as the actual Windows user account (e.g. [MACHINE\testuser]). If that user is the user who installed the database, then all is well and it has full access. For anything else, I get a "cannot log on" error - this much I expect.
So I want to permit logins for all other users, and I want this to work regardless of whether the machine is a standalone machine whose "domain" is simply the machine's own name, whether it is in some form of traditional peer-to-peer workgroup, or whether the machine is connected to a real domain. I also want it to work on XP and Windows Server 2003 (and ideally Vista also, but that can wait). When I try the following:
CREATE LOGIN [MACHINE\Users] FROM WINDOWS
I get this error:
Msg 15401, Level 16, State 1, Server MACHINE\SQLEXPRESS, Line 1
Windows NT user or group 'MACHINE\Users' not found. Check the name again.
Nor does it work with [Everyone] (that one has no domain/folder listed against it in any permissions dialogs on my domainless development PC). So I'm stuck and confused. It's taken me ages just to get this far. Any suggestions anyone?
Thanks in advance.
Hi,
Ensure your windows users have grant access for that db.
Refer for adding windows user/group to sql server for work around.
http://msdn2.microsoft.com/en-gb/library/aa905177(sql.80).aspx
http://msdn2.microsoft.com/en-us/library/aa163753(office.10).aspx
http://www.databasedesign-resource.com/adding-users-in-sql-server.html
Hemantgiri S. Goswami
|||Thanks, but that doesn't really help. One of these articles simply explains how to create Windows accounts, one explains how to use the sp_grantlogin storproc (which essentially issues the same SQL commands as I have listed above, and it gives exactly the same error messages), and the third tells you how to do it all manually, whereas I need to do it programmatically.
I should add that when I try to add a specific user account rather than the group account, the command works fine, so something is wrong with the attempt to add the group account. Does anyone know anything specific to groups?
|||I have found the answer. The issue is local versus global groups. CREATE LOGIN [domain\group] FROM WINDOWS works fine (i.e. a global group), but if you try it with a local group such as MACHINE\Users it will fail every time - this is a security feature of SQL Server. Which means that this approach will only work in a domain environment and not in a workgroup or on a shared single machine. The alternative is to use a nice GUI tool to manually add logins and users to SQL Express, but since SQL Express does not come with nice tools like Enterprise Manager, I may have to write my own and include it in the distribution. The group 'Everyone' fails also because it's what I would term a pseudo-group rather than either a local or a glocal group.|||
hi,
michael412 wrote:
I have found the answer. The issue is local versus global groups. CREATE LOGIN [domain\group] FROM WINDOWS works fine (i.e. a global group), but if you try it with a local group such as MACHINE\Users it will fail every time - this is a security feature of SQL Server. Which means that this approach will only work in a domain environment and not in a workgroup or on a shared single machine. The alternative is to use a nice GUI tool to manually add logins and users to SQL Express, but since SQL Express does not come with nice tools like Enterprise Manager, I may have to write my own and include it in the distribution. The group 'Everyone' fails also because it's what I would term a pseudo-group rather than either a local or a glocal group.
you can try defining your own local Windows group...
add a new Group via the "Computer Management" MMC snap-in, say you name it "MyMachineAllowedAccounts" and add the required windows accounts as members to that group...
then use the "nice ui tool" SQLExpress is provided with (yes, SQLEpress provides an official one 
) and execute a standard
CREATE LOGIN [MACHINE_NAME\MyMachineAllowedAccounts] FROM WINDOWS;
and it will succed as expected..
regards
|||The plot thickens. If I define a group MyGroup myself, then I can do CREATE LOGIN [MACHINE\MyGroup] FROM WINDOWS and it works. If I try to use one of the predefined groups such as MACHINE\Users, it fails. I don't at this point know whether this rule is constrained to local groups or whether it applies to global groups also. The group I added this morning in the office was a global group that we had created (i.e. it wasn't a predefined one), whereas the group that I failed to add was a predefined local group. I concluded that the important difference was whether the group was local or global, but now I realise that it could have been purely down to whether the group was predefined or created after installation. I don't see why SQL Server would want to prevent the use of predefined groups, and I've seen no mention of the importance of this distinction in any of the documentation. Can anyone throw any light on this?
Also, what is this GUI tool that is supposedly provided? I've looked through all the directories that were installed with SQL EXPRESS and haven't found one.
|||hi Michael,
The plot thickens. If I define a group MyGroup myself, then I can do CREATE LOGIN [MACHINE\MyGroup] FROM WINDOWS and it works. If I try to use one of the predefined groups such as MACHINE\Users, it fails.
it's only a "syntactical" problem as "how" you reference your predefined group's names...
CREATE LOGIN [BUILTIN\Users] FROM WINDOWS;
I don't at this point know whether this rule is constrained to local groups or whether it applies to global groups also. The group I added this morning in the office was a global group that we had created (i.e. it wasn't a predefined one), whereas the group that I failed to add was a predefined local group. I concluded that the important difference was whether the group was local or global, but now I realise that it could have been purely down to whether the group was predefined or created after installation. I don't see why SQL Server would want to prevent the use of predefined groups, and I've seen no mention of the importance of this distinction in any of the documentation. Can anyone throw any light on this?
check your syntax 
CREATE LOGIN [BUILTIN\Users] FROM WINDOWS;
Also, what is this GUI tool that is supposedly provided? I've looked through all the directories that were installed with SQL EXPRESS and haven't found one.
SQL Server Management Studio Express is not installed as part of the "client components" of the "standard" package and must be manually and separately downloaded and installed.. on the contrary, SQL Server Express with Advanced Services includes it and the tool can be selected to be installed as part of the installation process..
regards
|||Thanks Andrea - I think that's me sorted out now. Sorry to take so long to reply - I got tired and took a couple of days break from the PC.
Best regards,
Michael
No comments:
Post a Comment