Cannot Get CREATE LOGIN From A Windows Group To Work
Jan 28, 2007
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 [MACHINEASPNET]. This is easy to set up access for - I simply do a
CREATE LOGIN [MACHINEASPNET] FROM WINDOWS
and then within the actual database do a
CREATE USER [MACHINEASPNET]
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 estuser]). 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 [MACHINEUsers] FROM WINDOWS
I get this error:
Msg 15401, Level 16, State 1, Server MACHINESQLEXPRESS, Line 1
Windows NT user or group 'MACHINEUsers' 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?
Hello,I am putting together a row level security plan for our salesdatabase. I will give a brief description of the method I am thinkingof using to give you an idea of how I will need to be able to discoverthe group or login the user is using to access the data.I have a table called salesfact, it has all the sales info for all thebranches of our company. Each order(row) that is inserted has an entryin the division_number column to describe which branch the orderbelongs to. I have created another table(Branch_Folks) that has fourcolumns; username, windows_group, SQL_Login and division_number.I am using a view and SQL logins to control access to the data basedon the user, the SQL logins give windows groups access to the view.Only users that are added to the specific branch groups will haveaccess to the logins, but if a user is added to the group without mebeing notified, then he will not have a corresponding entry in theBranch_Folks table. Currently I am using the SUSER_SNAME() function todetermine which user is accessing the data from the view that I havecreated. The view uses this select statement to filter the data basedon the user & division.Select * from tsalesfact A, Branch_Folks B where SUSER_SNAME() =B.username and A.division_number = b.division_numberThis method works fine, but I will have to manually maintain the userlist in the Branch_Folks table in case a new user joins the windowsbranch group. I would like to use a function similar to SUSER_SNAME()that can determine the windows group or SQL Login a user is using.Does anyone know of a way to do this??Thanks a ton,Tim
I have created a linked server that connects a SQL 2000 database to a SQL 2005 database. If I use individual SQL or Windows accounts as local logins on the SQL 2000 instance, I can successfully query the linked SQL 2005 database. (For security we use the setting "For a login not defined in the list above, connections will: not be made")
If I try to use a Windows group as the local login, remote queries fail with the error "Access to the remote server is denied because no login-mapping exists"
Is it not possible to use a Windows group for the local login of a linked server?
If I run 'exec sp_linkedservers' the setup appears valid
Linked Server Local Login Is Self Mapping Remote Login SQL2005Serv DomainBRubble 0 SQL_Read_Access SQL2005Serv DomainWindows_Group 0 SQL_Read_Access SQL2005Serv DomainFFlintstone 0 SQL_Read_Access
I have a test environment set up at home with a small windows server 2003 Active Directory domain. I created a windows security group on the dc and added myself and another user to the group. I then use this group as a login for SQL Server and add the login to the sysadmin server role. This works exactly as I would expect on the Windows Server computer (which is also the Domain Controller) regardless of which computer I use to connect to this Windows Server computer. However, when I set up the exact same login on the local sql server instance of my client laptop running windows xp pro, the I get an error message (Login failed for user MYDOMAINdgolds...) when I attempt to connect to the local instance of SQL server, even though I am able to connect to the instance on the domain controller with no problems using this same client laptop under the same user account. The only way I am able to connect locally on the laptop under this account (which does not have local admin privileges on any box, for testing purposes) is to add the user account separately as a login, rather than as part of a group. I'm curious as to why being a member of a security group that is part of the sysadmin server role does not allow me to connect to the local instance of SQL Server on this client laptop. The windows server computer is running SQL2k5 Enterprise, and the client laptop is running SQL2k5 developer. Any insight into this would be appreciated.
Please direct me to the right forum if this is not the right one.
Upon installation of SQL Server 2005, I opened the SQL Server Management Studio, connected using Windows Authentication and went to the security node in the tree. I created a log in called "TestLogin". I restarted my server just to make sure everything took and when I tried to log in using TestLogin via SQL Server Authentication, I get the below error. I have mixed mode enabled as well as named pipes. I am not sure what the issue is or what to try next. I even get the same error when trying to use "sa".
Thanks,
- Joel
TITLE: Connect to Server ------------------------------
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476
Is it possible to create a windows authentication login for each of the computers on my network that will connect to sql server. I am developing a program that will be installed on multiple computers and we do not want to actually have to log in to sql server since we will have to log in to the server before opening the program and we dont want to have 2 logins. I would like to use windows authentication so that I do not have to program multiple connection strings in my program so that if more than 1 person accesses the database at the same time it will not cause a problem with using the same login. Is this possible and if so how would I go about setting up the logins. I am using SQL Server 2005 and XP SP2
I have a server that has 20 databases . I have tested with few users with different level of access and all of them were able to connect to the server and also see, select, update , delete from a particular database which is kind of weird because they do not have a user login associated or mapped to that database. I checked and no user is part of any group in AD that would give them permission to connect . I need a query that would find the permission path of a user. I already queried with xp_logininfo but I am not getting any thing.
I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
Oracle and Teradata are able to capture the windows/client login that connects to their database with a functionnal ID.I've searched around and I don't think MS SQL can do this...I administer SQL 2014 server and also SAS Enterprise guide product. I've created sql server logins for several teams so everyone shares the same sql server login which is used to bind the SAS library to my SQL server database.
However, when looking at activity monitor, I can't drill down to which user is executing a particular query. I only see the SQL login which is share amongs 200 other folks.On the other hand, when they connect from SAS to Oracle or Teradata, I can see the shared login + additionally their windows NT login used to connect to SAS which is awesome....they can't fool me by using a shared/funcionnal ID....i still see who's using that ID...
can anyone explain why, when i do a group by clause on the following data....it has no effect? Part SubPart Qty 120887 66743 83 120887 66743 100 120887 667443 25 553212 122987 119 553212 122987 67 here's my select statement: select part, subpart sum(qty) from partdata group by part,subpart,qty the resulting data looks identical to the original. what i was expecting was a return of just two lines:
Part SubPart Qty 120887 66743 208 553212 122987 186
I have have a column where there would be some duplicate records, but I have tried using the IN list, DISTINCT, and GROUP BY, and it does not give me distinct record set.
However DISTINCT and GROUP BY will work as long as my columns to display remain only one, but the minute I add more columns to my SELECT statement it does not make them distinct anymore.
Example 1 works, but Example 2 does NOT. In Example 2, I have added more columns otherwise it identical to Example 1. In Example 3, I was using the IN list, but the results of Example 3 is identical to Example 2 !!
EXAMPLE 1: SELECT DISTINCT email_address AS Email FROM email_address WHERE Send = 'y' and (DATEDIFF(DAY, Sent, getdate()) > 30)
EXAMPLE 2: SELECT distinct email_address AS Email, email_address_ID AS ID, Sent FROM email_address WHERE Send = 'y' and (DATEDIFF(DAY, Sent, getdate()) > 30)
EXAMPLE 3: SELECT email_address_ID AS ID, email_address AS Email, Sent FROM email_address WHERE email_address IN (SELECT DISTINCT email_address FROM email_address WHERE Send = 'y' and (DATEDIFF(DAY, Sent, getdate()) > 30))
Code Block WITH YesterdayCTE AS ( SELECT type = 'Members Joined Yesterday' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name ) , Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
MonthlyCTE AS
( SELECT type = 'Members Joined Last Month' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
, Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
YearlyCTE AS
( SELECT type = 'Members Joined Last year' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name ) , Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
combinedCTE AS
( SELECT * FROM YesterdayCTE UNION ALL SELECT * FROM MonthlyCTE UNION ALL SELECT * FROM YearlyCTE
)
SELECT * , Members = Borrowers + Depositors FROM combinedCTE
But I get the following error message.
An error occurred while reading data from the query result set. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (Microsoft Report Designer) =================================== Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)
Does anyone know if I have created a replication using SQL Server 2000 (sp3a), .Netframework 1.0 with Pocket pc Sql Server CE 2.0 will work or not in pocket pc windows mobile 5.0 environment (with the .netframework for pocket pc(netcf.core.ppc3.arm.cab) and sql ce file (sqlce.ppc3.arm.CAB) installed in pocket pc)?
I have it working in pocket pc Windows mobile 2003 version. But, after I tried to install the exact same cab files and system into Windows mobile 5.0, the replication fail.
I am trying to configure Reporting Services to allow a domain group access to reports. I am able to configure the domain and group (mydomaingrpname) in both Report Manager and BIDS. I'm sure I entered the correct name because I purposely misspelled it and received an error. I think this tells me it is finding the group correctly.
However, when my test user goes to Report Manager, there are no folders displayed. I checked and he is in the domain group I am using. If I explicitly add him (mydomainandy) to the folders, he can see them and execute the reports.
After searching the forums and other websites, I have checked IIS is using Windows Integrated Security and not anonymous access.
hi guyz i am trying to trap the duplicate records . cannot trap the below dupes any idea. i also used ltrim and rtrim it still thinking it is a duplicate but it is not. what is the best way doing it.
select count(*) , muzenbr from muzealbums_1 where muzenbr = '1000082' group by MuzeNbr, PNOTES, CNOTES, CAT3, Performer having count(*) > 1
MuzeNbr nvarchar no 14 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS PNOTES nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS CNOTES nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS CAT3 nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS Performer nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
I am busy looking at the WIndows 2003 Comptu Cluster setup (Well I am loading it)
And I am interested to know if any body has run SQL 2005 Enterprise on this platform before ?
I know that Windows Compute Cluster uses a JOB scheduler to run tasks and this can be customised for each task , to allow it to use more resourses or less .
I also know that SQL 2005 is much more capable of running cluster mode with database replication and log file shipping
I want to know is of I load SQL 2005 on a Clustered Compute setup will it handle the SQL jobs, database requests with out the scheduler.
The question might be a bit tricky but any help is appricated
Greetings, One of the DBs on my mssql server should ask for a login and password to change content. The content/DB is access via web page. Any suggestions as to why it has suddenly stopped challenging the user for login? I checked the services and all the necessary ones seems to be running.:confused:
I have a mobile device application using mobile sql 2005 replicating with sql 2000 in a x86 environment. This works fine!
I'm having issues getting this to work under Windows Server 2003 X64.
I've got all the components installed under the X64 environment including CLR 2.0 X64 and the mobile sql tools. the but when I run the Configure Web Synchronization Wizard I get the following error. SQL Server 2005 Mobile Edition Server Tools were not found on the IIS server. Run the SQL Server 2005 Mobile Edition Server Tools installer....
My question is: Were do I get the X64 version of these tools?
sqlce30setupen.msi sql2Ken@P4.msi
The SQL environment is X86 as follows: SQL2000 SP4
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I've got a Windows login, which I cannot drop. I have checked about everything I can think of and can't find a connection to this user. Is there a script which anyone has which will list all object referencing this users? SQL 2005 ENT x64. It's a windows domain user.
Hello, I do not succeed in authorizing the access of a group Windows NT to a linked server, on the other hand if I specify user's name it works. Please inform me. Pascal
We use local server windows groups to allow or disallow use of our SQL databases. We are now being asked to move off of our current server. My thoughts are to dettach and attach the database. What is going to happen to all my permissions based on the user groups of our old server? I intend to create the same group names on the new server but they will be newservergroupname as opposed to oldservergroupname. Is it just a case of scripting the database user, roles and object level permissions an manually changing oldserver to newserver?
I am getting error when I try to create a windows group and set default schema to db_owner.
Error message
Alter failed for user 'Domainuser'.(Microsoft.Sqlserver.SMO)
Additional information An exception occured while executing a Transact-SQL statement or batch. (Microsoft.Sqlserer.ConnectionInfo)
The DEFAULT_SCHEMA clause cannot be used wth a windows group or with principals mapped to Certificates or asymmetric keys.(Microsoft SQL Server, Error:15259)
Does anyone know how windows authentication works in SQL Server 2005 with regard to group membership? If a user is a member of two NT groups that have the same permissions in SQL then which group will be used to authenticate their login? The reason I ask is that logins have default languages associated with them so if a user is a member of NT group A with a default language of British English and is also a member of group B with a default language of US English then which one will be used and will it be the same one every time?
Using Bulk Insert Task extensively in our solution. Everything was working great till we deployed it in stage columns. The database server is different from application servers. We have ASP.NET web services driving SSIS packages on application server. After struggling thru several security issues to get this working (ended up creating an application pool with a domain account) we are now stuck with this problem. On a different note still don't understand what specific security permission is available to domain account that makes it work.
Read in some blog that SQL Server 2005 SP2 Beta had this (Bulk Insert) fixed but not in final production version. Is there a specific reason why this is so?
SSIS and the API is quite easy to work with but associated security and deployment issues are not always clear. A lot of answers seem to be coming from end users - thanks a lot to all for sharing your experiences - sadly not presented clearly in SSIS documentation.
Hello, I'm having a problem using Windows Accounts to login to a SQL 2005 Server. Here is my setup. The SQL server and web server are separate machines. I'm also not developing directly on the web server. SQL Server - Windows 2003 Server- SQL 2005- Set to use SQL and Windows AuthenticationWeb Server- Windows 2003 Server- IIS 6.0 - Anonymous Authentication is disabled - Integrated Windows Authentication is enabledApplication web.config: <?xml version="1.0"?> <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"><appSettings> <add key="ETR_Environment" value="Dev"/></appSettings> <connectionStrings> <add connectionString="Data Source=sql-dev-server, 1179;Initial Catalog=ENV_ETR;Integrated Security=SSPI;" name="ETR_Dev"/> <add connectionString="" name="ETR_Prod"/></connectionStrings> <system.web> <compilation debug="true" strict="false" explicit="true"/> <pages> <namespaces> <clear/> <add namespace="System"/> <add namespace="System.Collections"/> <add namespace="System.Collections.Specialized"/> <add namespace="System.Configuration"/> <add namespace="System.Text"/> <add namespace="System.Text.RegularExpressions"/> <add namespace="System.Web"/> <add namespace="System.Web.Caching"/> <add namespace="System.Web.SessionState"/> <add namespace="System.Web.Security"/> <add namespace="System.Web.Profile"/> <add namespace="System.Web.UI"/> <add namespace="System.Web.UI.WebControls"/> <add namespace="System.Web.UI.WebControls.WebParts"/> <add namespace="System.Web.UI.HtmlControls"/> </namespaces> </pages> <authentication mode="Windows"></authentication> <customErrors mode="Off"></customErrors> <authorization> <allow users="XXXWilliam.Klein"/> <deny users="*"/> </authorization></system.web></configuration> The reason why I want to use the windows login to connect to the database is the application needs to keep track of who did what when entering and updating data but still keep them using there windows login accounts. So using a generic account will not work. What keeps happening is I keep getting this error: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. When trying to connect the database. I've tried this on two web servers on another I get something slightly different: Login failed for user 'XXXWeb-Server$'. Anybody able to give me any suggestions on how to fix this?
I have created a Windows user login and have granted it the appropriate roles (including a revoke). As a test I also created a standard user and gave it the same roles as above. When I run a query against the revoked table, the results are unexpected. The Windows user can run the query, and the standard user gets a permissions error (which is what i expect).
Does anyone have any ideas as to what is happening here? I am still confused.... Does it have anything to do with the public permission that much be granted?
My platform is mainly windows nt4 server with two windows 2000 member servers. We recently acquired a higher spec machine to replace the old sql 7 server that is on windows NT4 box. The new server is a windows 2000 box and I have installed sql 7 on it.
The problem is when I try to create a new login it does not give me the option to select my domain. When I select new login and go for windows nt4 authentication the drop arrow for Domain only has the server name and not the domain name. If I manually input the domain name it rejects it.
I do not have Active Directory or a local dns server running on my network as we have no intention of migrating to Win2000 until next year. Our internet service provider handles our dns issues.
