What is the standard for database security – windows login or sql server logins? User defined database roles?
We have earlier used a single sql server login with read/write permissions to tables & exec on stored procedures. Individual application users are stored in database tables & their permissions are handled by the application itself. But we are starting to get concerned about password issues with the sql login & also that when I see any connections on the database, all of hem show up with the same sql login. So I cannot make out which user exactly has a connection open.
To get around these issues, we are thinking to add NT logins for each user. These users will be part of an NT group and will be added as a user to the database. There will be a database role with exec permissions to required stored procedures (all read/write/update will be controlled through SPs) and the NT group will be part of this role. The stored procedures will be encrypted. So if a NT user logs in directly to the database, he won’t be able to read from the tables or SPs and access will be limited through the application.
I will be able to see each login associated with a connection.
What do you all think about this security set up? What are other issues I need to be concerned about? What are other popular database security models?
I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:Â
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
I had created 2 Sql server instance in 2 servers created using VMware. From the primary server I log shipped the required databases into the secondary. Both the servers were in the same domain whose active directory was also in another server in the same virtual lab environment. My question can we have the primary sql server in one domain and the secondary sql server to which the logs are shipped in another domain by including a router also between the 2 networks for connectivity?
I have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.
Msg 262, Level 14, State 1, Line 1 CREATE DATABASE permission denied in database 'master'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
I have a script that automates some db drop/restore operations and bringing the database to single user mode is part of it: ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE...I want this to executes under a login, that has restricted privileges, so I've created a login and granted it a dbcreator role + ALTER ANY DATABASE privileges.
Problem: When I run the script against a database with an active/sleeping connection:It fails when using the restricted login: "Msg 5061...ALTER DATA BASE failed because a lock could not be placed on database ..."It completes successfully when using a sysadmin login According to stackoverflow.com the solution is to kill the active/sleeping connections to the database, before ALTER-ing it, which works fine, but the question is....
Questions: Why the "ALTER DATABASE..." statement works under the sysadmin login, but not under a dbcreator one?Does this mean the sysadmin login kills the connections to the target database in the background?Is it possible to grant additional privileges to the restricted login, so the "ALTER DATABASE..." statement won't need preventive killing of the connections?
i have a development database that has updates and changes to a production database. rather than go through individually and alter all relevant tables and stored procedures, id like to back up the database on the development side and restore it on the production side as the production database. is there a way to restore the database on the production server but preserve all the security settings (ie logins and such)? i noticed on our development server, that if i try to restore the database with my development database, it overwrites the users and/or if the user is the same on both, it removes the login name for that user.
I have just built a new SQL7 server (SQL SP3 and NT SP6a) server and need a bit of advice on implementing database security.
This new SQL server will be used by several projects which require a SQL database, our Personnel system being one of them. Since the data in the Personnel system is obviously of a sensitive nature I need to restrict access to the data. Client access is controlled via a client application but I need to restrict Enterprise Manager, Query analyzer ... type of access.
We have a team of people who look after the servers on a day to day basis and these are all Domain Administrators. By default these people will be able to browse into the Personnel database and read the information.
How can I restrict access to a SQL database so that only named users (SQL and NT) can access a database ? If database level access is required then we would need to obtain the SQL user password from Personnel which will then allow mainenance access, after which the password si changed by Personnel.
I am working with a development team and right now there is no security on the server everybody uses the sa account including our dts and jobs. I have talked to my boss about it and he wants me to come up with a guidline
What will happen to our jobs if i change sa password. also dose anyone have a little guidline sample?
Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).
Is there a way to create and encrypted database file?What do people do when data security is important at the file level? Inother words, you don't want anyone to be able to take the database file (orfiles) and extract data from them.Ideally, I want a file the is absolutely encrypted on disk and that isdecrypted for data access. The problem, obviously, is that this would be avery costly (cpu time) approach as you couldn't create a decrypted image ondisk (this would expose the data).Are all database systems then, non-secure?Thanks,-Martin
i a the user of sql server 2005 on window server 2003. i want to knowthat how can i prevent my database from other user logins because allof them are sysadmin type.and i am also looking for database concurrency control methods.if any one know about this plz mail me answer on this mail idJoin Bytes!thanx in advance
Hi, does anyone have the name of the command-line utility that creates a security database on SQL Server 2005 with the corresponding tables (users, roles, applications)? I remember using it in an exercise some time ago and I can't find it anywhere.
Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).
I am using sdf database file for stroing improtant data. That file is password protected. But I want to apply some more security constraintson on it. May I know What are the possible ways to secure my data? How I use encrypt feature? so that if anyone open sdf file he cant see actual data into it.
We are using VB as frontend and SQL Server 7.0 as backend applications in our company. The users are using 'User DSN' to connect the databse with NT logged in user security. User can connect the database if he is having the permissions on the database. We are not using SQL Server authontication like 'sa' user. Till here it is working fine. Our problem is, We want to avoid the users to connect the database using SQL Server Enterprise Manager. We have the SQL Server Enterprise Manager software installed on some machines. because some times we may need that. Right now the users (If he is having the permissions on the database) can directly open the database using Enterprise Manager and they can manipulate the data directly.
We need only specific users like The users who are in System Administrators group has to access the database using Enterprise Manager.
Hi, I need to write some T-SQL scripts to perform a database audit of several SQL Server 2000 databases that tracks all superuser logins and access to tables. I can do this in Oracle but I am lost with MS SQL Server. Can anyone point me in the right direction? Thanks!
HiWe have developed and deployed a database which contanis very sensitiveinformation. Our client is now concerned about the security of the backups. In essense, if someone in the organization can get hold of thebackup of the database, he can simply restore it on any sql server inthe world with sa permission.I know Microsoft provides flexibility of adding the "Password" in theBackup t-sql statement but it wouldn't be of much use if the back uptask is saved as a script and password will be written inside thescript.your suggestions are really appreciated!Thanks
Hello, another question from a newbie to mssql.Is there a way of allowing access to database only by providing username andpassword (disabling trusted connection and preventing administrator toaccess database through enterprise manager or otherwise without supplyingusername and password)?What I would like is that my application upon instalation creates databasewith appropriate tables (already done this) and somehow solely creates andmanages user list and passwords so that there is no access to database otherthrough application. (Application would create a backdoor account shouldsomething go wrong)
Sorry, the prior message was multi-posted.Here's a cross-posted version. Please disregard the other one.Is there a way to create and encrypted database file?What do people do when data security is important at the file level? Inother words, you don't want anyone to be able to take the database file (orfiles) and extract data from them.Ideally, I want a file the is absolutely encrypted on disk and that isdecrypted for data access. The problem, obviously, is that this would be avery costly (cpu time) approach as you couldn't create a decrypted image ondisk (this would expose the data).Are all database systems then, non-secure?Thanks,-Martin
I am having some issues with SQL Express not being able to create a database because the SQL Express account doesn't have permission. During the install I have the user select the local system account. Even then it doesn't work. If I would go into the folder with the database and give the standard users full control the database will attach.
Is there a way to handle this? I have one install doing all of the work so the user really can't interact with the file system during the install. I am looking at Xcacls as a possible solution but is there something I can do in SQL to fix this?
Could somebody explain to me how does database security in the SQL Server Express environment works? It would seem that there has to be some level of "interplay" between the user's basic level of authority in Windows security on the machine (e.g., "Adminstrator" vs. "User"), the implications that that has on their level of access to the hard drive location where the database file is stored, and how security is configured within the database engine.
I am running SQL 2005, SP2 I have two databases (happen to be SMS (dbA) and SMS Client Health (dbB)) I have created a view in dbA that selects records from a table in dbB - view is called v_ClientHealthStatus The view works great with my credentials
dbA (the SMS db) has SMS Web Reports configured (IIS will run a particular report - which is a sql query). All reports work, except the one that tries to use the view, v_ClientHealthStatus. I get the following error:
An error occurred when the report was run. The details are as follows:
The server principal "NT AUTHORITYSYSTEM" is not able to access the database "dbB" under the current security context.
Error Number:
-2147467259
Source:
Microsoft OLE DB Provider for SQL Server
Native Error:
916
The NT AuthoritySystem user has been granted public database role and is in the schema db_datareader on both databases. I have even gone so far as to explicitly grant the user Select permissions
I ran profiler and it shows NT AUTHORITYSYSTEM as the account trying to access the database. I think the permissions are OK - and shouldnt the System account have full access anyway??
I'm using sql server 2005 express edition. I had so security problems that i got helps from this forum. Now i have another one :
In my system i'm using a database with sa password, it means to login and work with database structure and data user should enter sa password.
But when i deAttach db and transfer it to another computer, I can easily attach db to customers sql server without knowing sa password.
I want to say : Everybody can attach my db to their system without knowing sa password and without need to create user, I need security in customer side not at mine.
Please help me what's wrong? I know there is a solution that i don't know!
Hello every one We have made a application which can create and restore data base for MS SQL we also set the password . we want to make it secure . problem is that if data base files are copy from one system to other system and try to open then . they are opened with out asking data base Pass word can any told me solution so that our data base become secure Thank
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 have a requirement to secure the data in a new database so that noone can look at all of the data, including the SA / dbo.Should we give the VP the sa password and tell her how to change it soeven the DBA can't access the data?Can we somehow stop SA/dbo from looking at say , a salary column, in atable?I know I can do it for oth users but can it be done for sa??Thankspraim sankar
We have vendor that is implementing an employee self serve application for current and potential employees (employment applications). There is a web server in our DMZ that has the application installed but also on the server is a SQL database that has names and social security numbers. This server will also query the backend accounting server for earnings statements and W2s. We have a Cisco ASA as the firewall and SSL to protect client authentication from the Internet. There is no SSL between the web server and the accounting server. The fact that the SQL database on the web server containing SSN associated with names concerns me. It seems that none of this information is masked or encrypted and can be seen if the server was to ever be compromised.
My idea of such a service involves a web server that queries the backend database over SSL and presents the information to the user over SSL. No personally identifiable information would be resident on the web server at all, just a facade. That is not the case and it is not what we described to them as to what we want.
It seems they have installed it the only way they know how which is not secure, or maybe it is, that's why I am here. They have installed this at numerous locations and they actually wanted any and all ports open between the web server and the backend accounting server. It took us a while to get them to follow the rule of least privilege but we essentially had to do it ourselves.
Also on our main webserver for our Internet site I found the test database they used almost 2 years ago to test this application along with names and SSN. This was before I arrived and there is no encryption or authentication for this server. Is this good secure practice? All my training says no but it is hard to believe a mutli-million dollar organization is this ignorant. I guess it shouldn't surprise me, TJX didn't pay attention either.
I saw this thread which provide some good information but I am not a database admin and I am not familiar with SQL services, etc.
My questions are: Is their implementation secure? Does anyone know where I can find more info regarding web services and HIPAA? I read where 2 firewalls are required but would like documentation to show. Any suggestions on how to implement this securely?
