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.
Thanks!
Hello,
I am new user of SQL Server. I have some problems with these words. I want to make my database works in my specified permissions. I will specify permissions with schemas and these schema wants an owner. I want this owner should be my user. When creating a user it needs a valid login. I am selecting my login and it occurs and error says this login has an different user. I am specifying permissions with roles. But i can't make association all of them. I hope i told my problem to you as well. If you explain these words to me and tell me how can i do my database's works with my own schemas, users and roles i'll be grateful. Thanks for advices.
Happy coding...
After reading Books Online, I am still confused with Database Role vs Application role.
My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.
If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?
Hi there
Can anyone point me to a good tutorial or give me a run down on using Sql Application roles from my asp.net application. Books Online only give a run down on how to set it up - not how to implement or use it from my code. MSDN no help. Google - same thing.
Any help greatly appreciated.
Thanks in advance
We have a distributed app that creates it's own instance of sqlexpress when installing. This prevents anyone with sa rights on another instance from accessing our data directly (HIPAA compliance concerns). We are currently looking into making our app easier to install, and want to be able to attach our database to existing instance (if one exists), but still prevent the sa account of that instance from directly accessing/viewing our data. Are application roles the way to accomplish this, is there another way, or is this even possible?
Thanks in advance.
I may need to setup an application role(s) fro a SQL 2000 db that is being front-ended by VB6. There are 2 types of functions needed, and admin role with access to all tables, and a user role with access to only specific tables. I know from just a straight db roles, I could set up 2 roles, set their rights, and then add the users to these roles. My questions is if I need the same functionality for an Application role, do I need 2 Application roles, one for the admin, and the other for the user?
View 1 Replies View RelatedWe have an an application that was written using OLE DB (ADO) against a SQL 2000 Server that uses an Application role to give rights to the database objects. It connects, calls sp_setapprole and goes on. If the database needs to LOCK a record, it is creating a new ADO Connection and instantiating the Approle again. This model has been working fine up til now.
Now we are installing a SQL 2005 server for the latest version of the product we are working on and are running into an error. The error is
Error: 18059, Severity: 20, State: 1.
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.
It's happening when the second ADO Connection for locking a record is being created and the sp_setapprole is being executed.
One of my questions is what is the problem with executing the approle on a different connection? Our code has not changed, so obviously SQL 2005 is doing something different. The other is What can we do to correct this?
Is the resource pooling different? We had problems in the beginning with approles and figured out through research that we needed to add OLE DB Services=-2 to the connection string to turn off resource pooling.
Is there an extra step to using Approles in SQL 2005?
Any help would be greatly appreciated as we need to resolve this ASAP.
Thanks,
David
I want to test Application Roles security for our project, I guess it serves the purpose.
But the quesion I have is if a developer who can look at the application code know's the "Password" can he set the password from Query Analyser and get acess to the database.
Thanks!
Hi,
I have added two roles to sql server. One called Officeusers. The other AdminUsers
Added the appropriate logins to these roles. For example; james, john, ahmad to OfficeUsers and Mat, Nick to AdminUsers.
How can these roles be now used in a connectionstring? I can use each user login and his relevant password in a connectionstring to connect to sql server but not sure how/where/when the Roles come in to development.
i.e. do I need to use the role in the connectionstring? if so then what happens to the password.
Not quite clear about all these.
Thanks
Hello,
in SQL Server 2005 I have an application role that is being used to limit access to my server data from third party applications. Everything is working well, except changing the Application role password.
I set up a small form that allows an administrator to change the App Role password through the front end app. I cannot, however, seem to get the Password field in my approle to accept a parameter.
For example:
declare @newpassword varchar(128)
set @newpassword = 'foo'
ALTER APPLICATION ROLE MyApplicationRole
with PASSWORD = @newpassword
This procedure gives me a syntax error in the last line. It will accept a string in quotes but not a varchar parameter.
Ideas?
Hi, Am migrating my SQL 2000 legacy app to SQL2005 and am dealing with restrictions on the underlying system tables. Have taken advice that Granting VIEW state to all users is heavy handed (especially meta data access at SERVER level). Now looking at Module signing which is great. I can supply SP's which target the few System table/ information schema fields that I require. Now I Sign the Sp's cool, now I grant exec rights to the application role (doesn't work). Create a db role and put my users in it, okay grant role exec on Sp's (fine they work).
However my application runs under an application role always, so my users rights are ignored and it appears that its only the users not the approles who can benefit from the module signing ? I know I can switch too and from approle using cookies but I seem to be going round in cirlces here.
Essentially is there any 'EASY' and 'CONTROLLED' way that my application user who has no rights, who immediately switches to the application role can see the dbName (All rows ) from master.sysdatabases ?
Thansk for any advice
I have an application that segregates data into two differentdatabases. Database A has stored procs that perform joins betweentables in database A and database B. I am thinking that I have reachedthe limits of Application Roles, but correct me if I am wrong.My application creates a connection to database A as 'testuser' withread only access, then executes sp_setapprole to gain read writepermissions. Even then the only way 'testuser' can get data out of thedatabases is via stored procs or views, no access to tables directly.Anyone know of a solution? Here is the error I get:Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line38Server user 'testuser' is not a valid user in database 'DatabaseB'The system user is in fact in database A and B.thanksJason Schaitel
View 4 Replies View RelatedUnder SSMS I see the "securityadmin" Server role. Where in SSMS can I find the actual list of permsisions granted to this role? (not just a generic description of what this role can do.)
TIA,
Barkingdog
I have developed a site on my local machine utilizing the built-in security/roles provides by ASP.NET 2.0. I am trying to publish the web application to a production server. It will publish correctly, however the ASPNETDB is not recognized. The folder app_data is created with the proper database files. My question is how do I set this up to use the data I have created in this database? I've searched on how to accomplish this but haven't found any definitive answers. I have found ScottGu's blog to be useful, but I was wondering if there is a different way.
1 - Do I push the data to a SQL Server install via SQL scripts?
2 - Can I link to the ASPNETDB that is created locally on my machine?
3 - If I make changes to the application and re-publish, is there a way to keep from overwriting the ASPNETDB on the server?
I am a unsure of how the built-in roles/security works when you are ready to push something to production. If someone wouldn't mind pointing me in the right direction, it would greatly be appreciated. Any literature, advice, links, etc.. would be great.
- Thanks
Andrew
As far as i know all users that is assigned to "Public" database role by default cannot SELECT any tables which in the databases. But in my case i am restoring database from previous version of SQL Server 2005 to SQL Server 2008 R2.
The problem come when every new users that created can access all tables in the databases.
How to get "public" back to default permission settings?
Hi all:
[Posting this in the security forum because in this forum I found a related post.]
I have a problem with SQL Server 2005 and application roles and pooling. I needed to use application roles and I needed to use pooling at the same time for an application. I am using sp_setapprole and sp_unsetapprole. In order to ensure that the application role is always set and unset by the application, I actually developed a custom Data Provider based on the SqlClient Data Provider. I have a custom Connection and Command class that wrap the SqlClient versions. Upon opening my custom Connection class, I execute the sp_setapprole stored procedure. Upon closing or disposing the connection, I call sp_unsetapprole.
This works fine in 99% of my tests. However, I have three or four methods (always the same ones, but one only fails ever so often) that fail, but only under the following circumstances:
Pooling is turned on (but pool size doesn't matter)
I am using my custom Data Provider (using System.Data.SqlClient does not cause this issue... but I am also not using approles then)
When other tests have run in the same test run. I.e. when I run the failing methods by themselves in a test run, there is no problem.
So it seems to me that the problem is related to using application roles with pooling turned on. For scalability reasons, we cannot turn pooling off. When the methods fail, I see the following two (2) entries in the SQL Log:
Error: 18059, Severity: 20, State 1.
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.
I understand the error message somewhat. However, I am not sure why the "reset" of the connection occurs. My code does not call reset anywhere, so it must be something that happens in the background.
I am reviewing code to see if there is possibly a situation where the sp_unsetapprole procedure does not get called or does not get called successfully, but there is a lot of code (in many custom components).
I would like to know if anyone has a suggestion on how to solve this problem, or, find the code that may be causing the problem.
Thanks in advance,
SA.
Hi,I'm
building an asp.net website using web developer express and I have a
SQL 2005 express install on the same machine. Using windows security,
everything works fine for development.Now I need to get it working at the client site, where the web server and SQL 2005 Express are on different machines.I'd
tried using windows authentication but that didn't work, probably
wasn't doing it correctly. I'd like to try SQL Authentication, at least temporarily, since I
can replicate that on my laptop at home easily I think.I created a new SQL login but was really baffled by the screens showing roles and schemas, etc. There's so many roles to choose from and wasn't sure if this new login needed to own any schemas. I
did turn on remote connections and mixed-mode authentication, the
probably now is my asp.net membership provider login page isn't
authenticating correctly, maybe my permissions are wrong on the
aspnet_..... tables for users, roles, profiles, etc. Any advice?thanks,Bruce
Roles Table:
RoleID, RoleName
<RoleID1>, Allow_ClientFolder
<RoleID2>, Allow_ClientReportA
<RoleID3>, Allow_ClientReportB
<RoleID4>, Allow_CompanyFolder
<RoleID5>, Allow_CompanyReportA
<RoleID6>, Allow_CompanyReportB
UsersInRoles Table:
UserID, RoleID
<UserID1>, <RoleID1>
<UserID1>, <RoleID2>
<UserID2>, <RoleID4>
<UserID2>, <RoleID5>
<UserID2>, <RoleID6>
<UserID2>, <RoleID1>
<UserID2>, <RoleID3>
e.g.
when the <UserID1> clicks on the "home" link show only "Client" folder and when <UserID1> clicks on the "Client" folder only "ClientReportA" will show.
Here is the accessable map for each user:
<UserID1>
Client
ClientReportA
<UserID2>
Client
ClientReportB
Company
CompanyReportA
CompanyReportB
Question:
What is the best way to implement CheckAccess? At first I thought I can access the item names and I can test authorization through that, but AceCollection contains operation permission of the accessing object.
I have two different roles, each one with a dax filter. One is for filtering users that access by Excel, and other for filtering users that access by Reporting Services, respectively:
=CONTAINS(FILTER(V_FILTRO_C;V_FILTRO_C[IDPerfilGrupo]<>7);V_FILTRO_C[iCursoCod];[SK_CURSO];V_D05_DIM_UTIL[DSC_LOGIN_USER];RIGHT(USERNAME();LEN(USERNAME())-SEARCH("";USERNAME())))
=CONTAINS(FILTER(V_FILTRO_C;V_FILTRO_C[IDPerfilGrupo]<>7);V_FILTRO_C[iCursoCod];[SK_CURSO];V_FILTRO_C[IDUtilizador];VALUE(CUSTOMDATA()))
To create only one role that serves Excel and Reporting Services users, is it viable to use only the || (OR) operator?, is there any other regard i should take?
Coldfusion Web appls from Oracle to SQL Server 2005 - How to use Application Roles in Coldfusion.
Is there anyone who has used application roles in Coldfusion Applications? How would you set this up?
I know how to set up application roles. If you establish your application role and you move from one page to another, how would you run cfquery since you loose your initial user connection in place of the application role connection. Are there alternatives to using application roles in Coldfusion?
Sample code would be helpful.
TF
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?
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?
Thanks for all your help and creative ideas!
Rob
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?
Thanks
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!
View 1 Replies View RelatedHello,
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
SQL Server 2008 R2: How security log is much secure than application log?
View 3 Replies View RelatedWe 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.
All:
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.
Thanks,
Blue.
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.
View 2 Replies View Related
Hello,
I am facing a very odd behavior with SQL server 2005.
I have a database role with specific permissions to execute only some of the stored procedures in the database. I also have a SQL login/user attached to the role.
If I execute one of the stored procedures from the application (web application, with Microsoft enterprise library database block) I am getting a security error stating that I need select permission for the schema.
However, If I execute the same sp with the same parameters from SQL management studio, connecting with the same SQL login as in the application I can execute the sp without any security errors. The SQL management studio is in on a different server than the targeted database.
Are there any differences between executing stored procedures from SQL MS and from application when using the same SQL login?
Thanks,
IT
Here is my situation: I am creating a database driven ASP.Net web application that will be used over the internet. My ASP.Net application connects to my SQL server 2005 database/server by using a SQL server login. I am using the DPAPI API to encrypt my connection stings with a hidden entropy value for extra security. I am using the SQL login for obvious reasons, as my users will not have a windows login.
What I am trying to do: I want to limit this SQL login account to be able to just run/execute stored procedures and NOT access the tables or views directly. In my ASP.Net application I am using the MS applications data block, and I am using stored procedures for every single database access action. There is no inline SQL being executed from my web application.
What I have tried so far:
I created a new schema and made the above SQL login account owner of this schema. I then granted "Execute" permissions to the SQL login and DENY permissions to all other permissions.
I created an database role with "Execute" only permissions and DENY permissions to all other permissions.
What Happened: In BOTH of the above scenarios I tested a direct SQL statement against one of my tables, from my ASP.Net application and I was able retrieve data back, NOT GOOD, exactly what I am trying to STOP.
If someone could give me (Step-by-Step) guide on how to setup the situation I am looking for, I would be very grateful!
Thanks to all that help!
Hello everyone,
I don't know what category would be appropriate for this question but security seems to be close enough.
I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.
Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.
Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?
No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.
Any thoughts or ideas?
Thanks for your time, Adrian