Unauthorized Operation Setting Application Security Through VS2005 (Why?)
Feb 12, 2007
I'm attempting to setup the defaco MS security for membership and roles, using a newly created database under SQL 2005 (not express). I created the database using the aspnet_regsql.exe utility and that worked fine. I created my provider connection string logging in as 'sa' wit the proper password. All that seemed to work okay too. However when I attempt to change any of the settings like setting the authentication type or enabling roles, I get the follwing error message: The following message may help in diagnosing the problem: Attempted to perform an unauthorized operation. at System.Security.AccessControl.Win32.SetSecurityInfo(ResourceType type, String name, SafeHandle handle, SecurityInfos securityInformation, SecurityIdentifier owner, SecurityIdentifier group, GenericAcl sacl, GenericAcl dacl) at System.Security.AccessControl.NativeObjectSecurity.Persist(String name, SafeHandle handle, AccessControlSections includeSections, Object exceptionContext) at System.Security.AccessControl.NativeObjectSecurity.Persist(String name, AccessControlSections includeSections) at System.Security.AccessControl.FileSystemSecurity.Persist(String fullPath) at System.IO.File.SetAccessControl(String path, FileSecurity fileSecurity) at System.Configuration.Internal.WriteFileContext.DuplicateTemplateAttributes(String source, String destination) at System.Configuration.Internal.WriteFileContext.DuplicateFileAttributes(String source, String destination) at System.Configuration.Internal.WriteFileContext.Complete(String filename, Boolean success) at System.Configuration.Internal.InternalConfigHost.StaticWriteCompleted(String streamName, Boolean success, Object writeContext, Boolean assertPermissions) at System.Configuration.Internal.InternalConfigHost.System.Configuration.Internal.IInternalConfigHost.WriteCompleted(String streamName, Boolean success, Object writeContext, Boolean assertPermissions) at System.Configuration.Internal.InternalConfigHost.System.Configuration.Internal.IInternalConfigHost.WriteCompleted(String streamName, Boolean success, Object writeContext) at System.Configuration.Internal.DelegatingConfigHost.WriteCompleted(String streamName, Boolean success, Object writeContext) at System.Configuration.UpdateConfigHost.WriteCompleted(String streamName, Boolean success, Object writeContext) at System.Configuration.MgmtConfigurationRecord.SaveAs(String filename, ConfigurationSaveMode saveMode, Boolean forceUpdateAll) at System.Configuration.Configuration.SaveAsImpl(String filename, ConfigurationSaveMode saveMode, Boolean forceSaveAll) at System.Configuration.Configuration.Save(ConfigurationSaveMode saveMode) at System.Web.Administration.WebAdminPage.SaveConfig(Configuration config) at ASP.security_setupauthentication_aspx.UpdateAndReturnToPreviousPage(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)Anyone have any clue why this is happening? Do I need to add something to the database as far as users/roles go? I figured 'sa' would have free roam, but something permission-wise just isn't jiving.
Hi, can anyone help with this pls - it's getting very fustrating! there seems to be some messages and text on this issue (looks like a common problem that MS haven't published anything about yet!) but nothing I could make heads or tails off... so any help here would be very useful!
I'm using VS2005 (release, NOT beta), the new reportviewer web control and an ASP.NET page - really basic, nothing fancy. I've set the reportviewer properties to:
I'm testing the ASP.NET page is on my local machine - and the reportviewer is connecting to a remote test server (W2k3 SP1). When I view the page, then I get
HTTP ERROR 401 Unauthorized
Directory Security on the report server is Windows Authentication - W2K3 with SP1 and Reporting Services 2005 (release, NOT beta)
Directory Security on my local machine is Windows Authentication - XP SP2 and VS2005 (release, NOT beta)
I'm trying to install SQL Server 2008 on a virtual machine with Windows 2008 R2. Setup fails with the error "Attempted to perform an unauthorized operation". Looking at the installation log, I see these details:
I am using mixed security on the SQL server and have an NT group 'NT_GROUP'. The login for 'NT_GROUP' has been added to SQL server and has been explicitly granted access to only one database.
Using access database project (ADP), a user in the group is able to gain access to other databases (master, tempdb, msdb, etc.) as well.
It is very important to be able to prevent this access.
I receive the following error when i call report server web service from an asp.net application:
"The request failed with HTTP status 401: Unauthorized."
Here are the IIS logs on the reporting services server. When i open IE and browse to the web service i provide my user credentials and i can access the web service just fine. However when i call the web service via my asp.net application it looks like my credentials are not being passed??
We have built a .NET on SQL Server application with extensive business functionality and security. It uses SQL integrated security to control logon and access to the database objects. There is business logic built into the .NET code, so I don€™t want any chance that users access the tables directly through a tool such as Management Studio - many users need update, delete, insert on tables in order to use the applications functions. With integrated security they can logon through SQL Management Studio and change data in the tables directly.
How are people who are using integrated security for their business applications preventing logons through unauthorized tools or applications?
I have some developers who would like to debug t-sql in vs2005. One developer is a sysadmin and it works fine - this is the microsoft recommended solution . This one developer can have sysadmin, but the others cannot. I read a blog post from a sql server team developer that you can grant execute to master.dbo.sp_enable_sql_debug extended sp. We could possibly grant execute of this sp if it lets developers debug in development. We do know that they could maliciously elevate priv's to SA, but that is not so much of a concern, just that they cannot easily perform sa actions.
http://blogs.msdn.com/sqlclr/archive/2006/07/07/659332.aspx - has most of the information I've gotten about this topic. From the article I can't really tell if the user has to be sysadmin to execute the sp_enable_sql_debug - even if granted. From the behavior below, I'm guessing it does.
Do I need to do something else here? Are we taking the wrong approach?
Thanks!
My commands and results -
grant execute on sys.sp_enable_sql_debug to [samtest]
go
execute as login = 'samtest'
go
master.dbo.[sp_enable_sql_debug]
Msg 229, Level 14, State 5, Procedure sp_enable_sql_debug, Line 1
EXECUTE permission denied on object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'.
Hi everyone, I am attempting to drag the SQLDataSource control to my webpage to initiate a connection to my SQLServer Express instance on the same system. The control will drop correctly, but when I click on the "Configure Data Source" in the SQLDataSource Tasks popup panel, I get the following error message: Following error occured while getting connection string information from configuration."Cannot get web application service" It then sends me to the configure data source window where it asks me to choose my data connection. However, there is nothing in the pulldown, and the "New Connection" button does nothing. I have the SSE data connection defined and visible in the Server Explorer, and there is a connection string defined in the web.config file that points to the server and database I wish to access. I'm on day 2 of my excursions with VS2005 Pro, so I feel that I'm missing something huge, but otherwise obvious that's different in the configuration of VS2005 as opposed to VS2003, which I have no issues with. Thanks!Chris
I am working on a windows application (C#) that utilizes ODBC to acces paradox tables (works fine thought this would be the problem database) and a WinMobile database(.sdf) in order to sync table records. What I need to do is copy the .sdf file from the winmobile device then access it with this application to check for changed records on the PC and in the mobile database then copy it back to the mobile device via RAPI.
The program compiles fine but when I try to run the application I get this error. Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E) .
I have already copied the System.Data.SqlServerCe.dll to my projects bin folder and then added a reference to it in the projects Reference dialog. These were also copied to the bin folder of the project
sqlceca30.dll
The SQL Server Mobile Client Agent. Required for applications that connect to SQL Server by using replication or remote data access.
sqlcecompact30.dll
Provides the compact database functionality. Required if your application will use compaction.
sqlceer30[language].dll
Contains error strings for SQL Server Mobile-generated errors. Required for all SQL Server Mobile applications.
sqlceme30.dll
Contains code required by the System.Data.SqlServerCe.dll file. Required for all SQL Server Mobile applications.
sqlceoledb30.dll
Provides OLE DB connectivity to SQL Server Mobile databases. Required only if your application uses OLE DB to connect to the SQL Server Mobile database.
sqlceqp30.dll
The SQL Server Mobile Query Processor. Required for all SQL Server Mobile applications.
sqlcese30.dll
I also added using System.Data.SqlCE to the form and the dataset designer.cs
From this code in the dateset.Designer.cs file after the run is started the eroror occurs.
I have posted this issue for a week, haven't got any reply yet, I posted it again and desperately need your help.
The article http://msdn2.microsoft.com/en-us/library/ms365343.aspx says: Model Item Security can be set for differnt security filters, but when I use SQL Server Management Studio to set Model Item Security, it seems "Permissions" property surpass "Model Item Security" property. -- My report server is using Custom Authentication.
For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;
in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.
My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?
The article also says:
"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."
So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.
I can only get one result at a time but not both:
either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.
After I login into the Reports Server (<servername>/Reports1/Pages/UILogon.aspx?) the Home page is blank ( accpet for links to Home |MySubscriptions | Help in the upper right corner). Please read on before thinking you know the answer. I am using Reporting Services with Forms Authentication with a custom security extension. In the LogonUser methothd I validate the user and return True if the user is good. Should I be doing anything else in this method or any of the other methods in my custom extension? I'm asking because, I can use Sql Server Management Studio to login into the Reporting Server, view any folders such as the Data Sources or Models folder, but can't make any changes. In the Users table in the ReportServer database the user I'm logging in with has a UserType of 0 and AuthType of 3. I've tride changing these to match the BUILTINAdministrators user but still no luck. Also, I can not login as the BUILTINAdministrators because that user would not be in our database. Any help with this is appreciated. Thanks.
Does anyone know the best way to set up NT security. It seems a little confusing as to how to set up NT groups and assign permissions (where do the roles come into it?).
Hi I just deploy a report model and want use report builder to create ad-hoc using this report model. I want some entitis and attributes are not visiable for some user, so I config the model item security for this model. But no matter which user I use to login the report server, I always can access all the entities. Even I delete all the groups and users in "Permissions" property of the model, I still can access this model through report builder. All the user I used to test are local user of server with report service, my server is SQL Server 2005+SP2.
I am having a little trouble with my SRS installation. I have a fresh-out of the box server running Win 2003. I installed SQL & SRS on it. I have successfully deployed my report project and can run reports, etc.
The problem is, even when logged in on the console as the local admin, I can't seem to perform the usual administrative functions on the report server- simple things like hide items in list view. I have no menu options for any of the security stuff, either. I though that the local admin was able to do these things by default. I have a feeling that this has something to do with active directory & role membership? It is like the administrator only has guest privledges.
When I connect to the report server from management studio, I can see the roles but no information about users, no options ot add users to the roles, either. My goal is to simply add a user (Administrator) to the proper role to be able to configure the report server environment.
For what it is worth, this box is simply set up in my home office, no domain, etc. Please be gentle, I am mainly a SQL geek, very little knowledge of Server OS & Windows security.
We are rolling out mssqlxpress to our development staff and would prefer to alter their permissions so that they can only make changes using mssqlxpress (since it integrates nicely with VSS).
So are choices are to uninstall SQL Enterprise Manager and SQL Query Analyzer, or more preferably, alter their NT Permissions so that they do not have the capability to change anything in the DB's.
My concern is that it seems we can only set their permissions from DB level and not the application, does that sound correct? Has anyone else had to deal with this predicament and is there an alternative to uninstalling the SQL Client tools?
I have just reciently installed and started upgrading the last beta code to this beta and am having a problem conecting to my sqlinstance with the WebSite Configuration Tool.
We are looking to make our applications as secure as possible. I am interested in how well Application Roles work to make security tighter.
Have you used Application Roles. If you have, I'd like to know if it helped provide better security or not and if it did, how was it implmented in you production environment. I already know how to get it set up, I'm just wondering if it's really worth the trouble.
With my next application, I'm thinking about establishing a new security paradigm for my programs, with respect to SQL Server.
In all my previous applications, connections are established using SQL Server authentication. So, all my users may log-in under a single log-in, mapping to a single user in a database. My application then has to use its own security arrangement to determine who has access to what forms within the application. There are three major problems with this design: 1) the user name and password to connect to the SQL server have to be resident as strings somewhere in the application code (or ancillary files), 2) everyone connecting to the SQL Server "looks" the same, and 3) you have to give the broadest rights to everyone with the same login and then pare those rights down within the application itself.
By implementing multiple SQL Server Authentication logins I can mitigate problems 2) and 3), but the only way to eliminate problem 1) is to move to Windows Authentication.
Windows Authentication would allow me to resolve all three design constraints, but there is one problem that I see coming as a result.
If I use Windows Authentication, each user must have an independent login to the SQL Server. If I have an application that may have 4,000 to 6,000 users, does that means I have to have 4,000 to 6,000 logins set up on SQL Server?
Is that true?
I could, of course, generate a script to build all 4,000 to 6,000 users, but I am concerned about this.
Is this a "normal" arrangement that SQL Server has no problem-with?
Ok, i have read a few articles regarding application security and it sems to me the Microsoft just doesn't get it.
As others have posted, Vendor packages are not normally controlled by the IT staff... they do what they do. When an application is used for mission critical data, it becomes important to lock down that data as best as it can be under the limits of that application. Yet what is often lacking in a vendor package is thought about what opening up the database means.
We are using a Vendor package that does not allow for application security to be set. In other words, it uses windows authentication to grant Database access. As many others here have noted, this opens the database to manipulation via any other tool the user has at their disposal.
It seems that the general reply here is that we should get the vendor to change their package to allow for Application level security. Good Luck!
I am not a DBA (must be obvious at this point) and i do not know all the what's up and coming, but generally as a system support person, i must deal within the environment i am given. Here's what I see as the environment I am working under, the problem and the preferred solution.
Application only allows windows authentication.
The users must be allowed full access to the database in order for the application to perform updates as needed.
Security to specific data by company is controlled within the application to determine what a specific user can change.
Granting security to a user ID allows them to access and change data at will through an alternate product (E.G. Enterprise manager, MS Access etc.) without regards to the security built in to the application.
SQL Server knows what ID is requesting data and knows what application is generating the request (I have seen screen prints of the list of who is logged into the database and right alongside the UserID is the Apllication Name).
If SQL server knows these two bits of info, why is it so difficult to allow SQL server to append the Application name onto the user ID and allow access to the DB only via this combination? The permissions can be set up with some combination of UserID/Application name and no special security, no messing with special application passwords/aliases, no pleading with Vendors to make changes and best of all.... satisfying SEC rules for locking down DBs.
Am i missing something? Does anyone know how i can accomplish this simple task without stressing out the DBAs?
We have an application use Approle to read from database. If the client login to windows as administrator or a name that has the administrator rights, the application can get all data. If the client login to windows as a domain user that has limited rights, the application can't get all data. I run profiler and found that it seems, when application use approle to access a database, the login name is the domain user that log into windows. Is there anybody know what type of right the window login name should have in order to get all data from a database?
Second question, when I log in to window as domainusername( username is not administrator, but has administrator rights). In the profiler, I can see the application use this domainusername access database. However, under sql server login node, I didn't find domainusername. Is this because, the domainusername belongs to buildinadministrator?
I work on test SSRS setup and trying to give one user enough rights so she can download RDLs from server, but no matter what I do on Folder leverl, on report level her security are still only as a <Browser>. Structure of our Server is:
Home/NewReports/Misc/Report01.
I'm checking those in <Folder Settings>/<Security> where this user is OK (Browser, Content Manager, Publisher, Report Builder).
So she looks OK in all folders Home/NewReports/Misc, but on report level she still only a browser.
Our db team tried everything on SSRS server working with Site settings and Folder option, how to make that report inherit security ?
Looking for a consultant with solid experience in application anddatabase security for a project in the GTA.CONTACT---------------------------------------------------Please send your resume to:Join Bytes!
We're having a bit of a problem getting Integrated Security to work with a .Net 2.0 application and SQL 2005. While we're tweaking permissions on the SQL-side, we came across an account "Application Login" and wondered what its role is. First, our problem:
Currently, the users in the AD group get a connection error. This group is defined as follows at the instance level:
role: public user mapping: to the database without any default schema securables: none status: grant and enabled
At the database security level:
general: none securables: execute on all (100+) stored procedures
And we gave them "Execute" on the database itself.
A little background: we had detached and copied this database from one server to another. So we suspect that the Application Login may have been modified/corrupted, even though it appears to be identical between the original and the copied databases. So we redefined it on the copied DB to match the original. Another group, which is defined as dbo on the database, has no problem at all connecting and running the application.
The Application Login has Execute permissions on all stored procedures and Delete, Insert, Select, Update, and View Definition on the ChangeLog table. It also has db_DataReader, db_DataWriter, and db_ddlAdmin roles associated with it.
Is there another SQL login required for initial connection to the database even though Integrated Security=SSPI is used in the connection string?
Does anyone see where we may be missing a security setting for the non-dbo user group to connect to the database?
Thanks very much for any suggestions, ideas .... Cheers, Tess
Dear members,I'am thinking about the "best" way to connect to my sql-server viaADO.Net (if there is a best way).My application uses the sql-server very intensively. At the momentevery database-operation opens a connection, executes and then closesthe connection.Another way would be to use only a single connection to the database,which is opened when the application starts.What do you think is the better way to get a high performing sqlserver: using one single application connection vs. using oneconnection for every operation?Cheers, Sebastian
Greetings Running SQL Server 2005. The developers on the project can see and edit stored procedures from within the Visual Studio IDE (via Server Explorer) but when they connect through management studio, the stored procedures do not show up at all.
Is there a seperate security setting specifically for management studio?
The user has:
The dbCreator Server Role
Is mapped to the development database as dbo with datareader/datawriter/db owner/public role Is mapped to master reader/writer/public Is mapped to model reader/writer public Is mapped to msdb reader/writer public Is mapped to tempdb reader/writer publuc
This is probably more security than the user needs, but was grasping at straws to let them edit stored procedures...
Hi, I would like to demonstrate mining temporary models in an ASP.NET application.
Creating, trainning, predicating actions are all witten at C# codes as follows:
Code Snippet
using (AdomdCommand cmd = new AdomdCommand()) { // Build temporary mining model cmd.Connection = asConn; cmd.CommandText = "CREATE SESSION MINING MODEL " + modelName + " (" + "HCVS_MemberId Text KEY," + "HCVS_MeasureDate DATE KEY TIME, " + "SysPressure LONG CONTINUOUS PREDICT, " + "DiaPressure LONG CONTINUOUS PREDICT," + "Pluse LONG CONTINUOUS PREDICT" + ") " + "USING Microsoft_Time_Series(Missing_Value_Substitution='Mean' ) "; // Periodicity_Hint = '{12}' cmd.ExecuteNonQuery();
// Train Data cmd.CommandText = "INSERT INTO " + modelName + " (HCVS_MemberId, HCVS_MeasureDate, SysPressure, DiaPressure, Pluse) " + "OPENQUERY([Healthcare], " + " 'SELECT HCVS_MemberId, HCVS_MeasureDate, SysPressure,DiaPressure,Pluse" + " FROM v_VitalSignForecast WHERE HCVS_MemberId=''" + id + "'' AND HCVS_MeasureDate>=''" + from.ToShortDateString() + "'' AND HCVS_MeasureDate<=''" + to.ToShortDateString() +"'' ')";
cmd.ExecuteNonQuery(); // Predict upon the Train Data. In addition, the standard deviation of each predicated value is retrieved cmd.CommandText = "SELECT FLATTENED " + "( SELECT *, " + " SysPressure + PredictStdev(SysPressure) AS [SysPressure_PlusStdev], " + " SysPressure - PredictStdev(SysPressure) AS [SysPressure_MinusStdev] " + "FROM PredictTimeSeries(SysPressure, " + fDays + ") AS SysTable " + ") " + "FROM " + modelName ;
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
DataSet sysDS = new DataSet(); adapter.Fill(sysDS); The problem is that I do not know how to configure my Analysis Service Server to let ASP.NET account can utilize it. And ASP.NET account in trun impersonates the account who is authorized to use Healthcare DB in the Openquery. Please give a help. Thanks a lot.
We have a Web application that requires user authentication. We have a Login Page for user's to login and be authenticated. We also manage the user accounts using SQL Server Membership Provider. We created aLogin user in SQL Server and used this user's privileges to connect to SQL Server in our website's connection strings. Example, in our Web.config file, the connection string is:
<connectionStrings> Â <addname="MyConnectionString"connectionString="Server=MYSERVERNAMESQLSERVER2012;Initial Catalog=MyDatabase;User Id=TUser;Password=MyPassword"providerName="System.Data.SqlClient"/> </connectionStrings>
The problem now is when a user logs in, it's throwing an error that TUser's password has already expired. We did not enforce the password policy and expiration date for this user. So, I'm wondering why it's giving me this error.
I am writing an Internet/Extranet based (ASP.Net 2.0) web application that uses SQL server 2005 as the database. I am using forms authentication on my web application. I am also storing the connection string to SQL server in my web config file. The conn string is encrypted using DPAPI with entropy. I currently have created a SQL login account on my SQL server for use by the web application. This is the user ID I am using in my conn string. The reason for this is because all persons using the application will NOT have a windows login.
Here is my question: The login I created currently has defaulted to the "dbo" role and therefore has "dbo" rights to the database. I want to setup up this login account so that all it can do is execute stored procedures. I dont want this SQL login to be able to do anything else. In my application I am using stored procedures for ALL data access functions, via a data access layer in my application. Can someone guide me step by step as to how to setup this type of access for this SQL login.
