Does anybody have a link to either of these two documents. My company is getting ready to go through an audit and we need some firepower and to know what is expected. Any help with obtaining microsoft SQL Server 2005 best practices documents is appreciated.
-Kyle
I would like to refer to the following technical article
SQL Server 2005 Security Best Practices - Operational and Administrative Tasks
http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx
Among best practices for SQL Server service accounts on page 8, it is recommended to 'use a separate account for each service'. I created separate account for each service as advised and assign account to relevant Windows group created for each SQL Server service during SQL setup.
Now when I run Best Practices Analyzer, its report seemed to contradict what the above article said. For example, BPA reports excerpts:
"We recommend that the service SQLBrowser on host MachineName be run under Network Service Account". I get similar recommendation for SQLSERVERAGENT account as well. Most importantly, it recommends that MSFTESQL be run under SQL Server Service Account.
Can anyone of you shed some light on it?
Thanks,
Asaf
Is there anyway to really keep replicated SQL Server 2005 Express data secure when it is on a laptop and the laptop is stolen or lost. What would prevent someone from just reinstalling SQL Server on the laptop and attaching your database, or just attaching your mdf file to their own SQL Server. Is there anyway to keep that data secure in that scenerio?
Thank you
Hello all,
I'm finding the documentation online to upgrading from 2000 to 2005 fairly poor.
What are the upgrading options and which one is the best option for a web server database.
Also what are best practices pre-upgrade, during upgrade and post-upgrade.
Cannot find much online.
Also since this question will be asked often might as well make it a sticky.
Thanks
Re: Best Practices (security): Should SQL Server (2005) *not* be installed on the same physical HD as the Windows OS (Server 2003 R2) ?
Hi,
We're setting up some new servers, and today I'm looking into best practices for the SQL Server Setup portion of it.
The servers have include 2 x 250G HD, and from what I've read, where IIS is concerned, it should not be installed on the drive that has the OS on it, for security reasons. I was wondering if the installation of SQL Server should be on the non-OS drive as well ?
Thanx,
Barry O'Neill
I would like to know best practices for setting up my environment. To date, I've had everything running on a single server. That would include the database engine, SSIS, SSAS and SSRS. The box configuration is dual hyperthreaded 3.6GHz Xenon with 4GB of RAM on Windows Server 2003. I just received a much larger server and want to configure it to maximize our environment. The new box contains four 2.6GHz Quad Core processors with 16GB of RAM. I would like to know if I should split the ETL and database engine from SSAS and SSRS, or should this box have enough horse power to house it all and use my other box as a dev environment. Also, we are planning to purchase Performance Point 2007 primarily for PAS and Scorecard Manager so please take that into consideration as well. Any comments are greatly appreciated.
Thanks.
Microsoft recommends using Windows authentication instead of SQL Server authentication in SQL Server 2005 for improved security. What are the Microsoft best practices for implementing this? Will be helpful if someone also provides some links that talks about this....
View 5 Replies View RelatedHello,
In general, with the introduction of schemas in 2005, is it considered "bad practice" to tell people to create new tables in the "dbo" schema?
Our product documentation contains a "quick start" guide for users who just want to get the product up and runing. We suggest that the customer creates a database for our application. This database is configured with a user that is assigned to the 'db_owner' role (we want to keep things as simple as possible) that we use to connect to the database. This database will only be used by our application. In this situtation is it okay to use the "dbo" schema, or should we consider creating another schmea for all our tables?
Are the any "best practices" for using schemas in SQL 2005?
Thanks,
Brenden.
I am re-posting this from the security Forum where it remains un-answered.
OK, Here's the set up.
I have a Windows 2003 box, soon to have SSL installed
On it is IIS 6.0, SQL 2005 Standard Edition (5Cal user lic)
SOON I'll have a prod enviornemnt where a web app being served by IIS is accessing SQL. I can go into SQL and set up a user account, call it MyAppSQLAcess, and code that into the connectionn string and lock it down to the tables/db it has access to. Or I can do it w/windows authentication, or I can do it a number of other ways, the question is this:
What is the best way for an asp.net app being server by IIS 6.0 to access data from SQL 2005 server when they are all on the same BOX? WRT Speed and security?
Thanks
Dan
I have just completed a default install of MS SQL Server 2005 x64 edition on a 1 processor Quad core, 8GB, Windows Server 2003 Standard x64 R2 machine in an Active Directory Domain. It has 2 arrays configured with 2) 36Gb drives mirrored for the OS as C: and 3) 147Gb drives in a RAID5 array as E: drive.
I am simply the server guy who was asked to install SQL server for some projects in the future. The company will be bring in people to to the dev.
I wanted to setup the server as best as I can and so I've been searching around for "best practices" for fresh installs.
Anyone have any suggestions on things to configure/setup to improve the performance of the default install?
In addition, I want to redirect the databases to the E: drive and separate them from the OS drive (C:). Does anyone have any information on doing this? I have searched and found a few articles, but thought I would ask first.
It is probably noticeable that I have limited SQL configuration skills.
Thanks,
A simple IT guy trying to build a system right
Greetings
I have a scenario where a table's after insert & after update triggers write to a history table. On this particular table, the history rows are quite small, so the occassional deadlock condition is inevitable.
In SQL Server 2000, I taught the application guys to handle these kinds of exceptions themselves, sleep a second, and retry. In 2005 (and I assume 2008) we now have TRY CATCH in T/SQL.
What are the pro's and con's of catching deadlocks in your T/SQL code (triggers or sprocs) .vs. the "old way" of catching it at the application level? I'm sure this has been discussed before, but search returns a lot of unrelated postings...
I'm thinking it's better to catch this at the application level, under the assumption that it's better to make the client(s) do the work than the database, but I'm sure there are considerations I'm not taking into account here. What experiences do you folks have to share on this?
Thanks!!
Does anyone have any advice or useful experience configuring memory management for SQL Server 2000 and 2005 instances residing on the same box? I'm looking for advice on whether I should just completely leave it up to SQL Server and Windows to allocate memory or should I attempt to put ceilings on certain instances.
Any help is appreciated!
We have a live OLTP database for which we create full backups every week and differential backups every day. Recently we added an OLAP database, which we need to update daily with changes from the live database.
This is the process we are planning to use.
1. Restore last full OLTP backup.
2. Apply the last differential OLTP backup.
At this point we should have a replica of the live OLTP database.
3. Update OLAP database based on the OLTP replica database.
4. Delete the OLTP replica database.
Two questions.
1. If different from the process above, how is this OLTP-to-OLAP transformation typically done in the industry?
2. What is the best way to implement this process with SQL Server 2005?
Thanks.
Hi all !
I have a question regarding the security of SQL Server 2005 Express Edition.
What securities options that SQL server 2005 EE provide for its users?
I know that after I deploy my website to a hosting company or a web
server, my database file will be on the net. That means everybody can
type in the file name and download my database file then open it. Just
like that. For example, my database filename is EXAMPLE.MDF. then,
someone just goto my website, say
http://www.cool.com/app_data/example.mdf, and download the database.
To prevent that to happen, what the securities options that available to me beside putting password on my database file?
Thanks for taking time to answer my question. Any help will be appreciated. Have a good day!
Hi all,
Could anyone suggest me which is the recommended authentication mode for web applications with MS SQL Server 2005.
Also let me know how the new security features of MS SQL Server 2005 can be used for secured application access.
Thanks in advance
HHA
(1) To prevent unauthorised database access, is it ADEQUATE to delete / disable the BuiltInAdministrator login and the guest (database) user ?
(2) How can I delete / disable the BuiltInAdministrator login
in SQL Server 2005 Express ? It didn't allow me to disable or delete it.
(3) How can I delete / disable the guest (database) user
in SQL Server 2005 Express ? It didn't allow me to disable or delete it.
HI,
I am just starting out with SQL Server 2005 and really getting in a muddle with al the security stuff.
It seems i have LOGINS, DATABASE USERS, Server Roles, Database Roles, SCHEMAS and somehow they all tie in together. I am using Microsoft Press "SQL Server 2005 Implementation and maintenance" but it really isn't doing a good job of explaining it to me.
Can anyone point me to a reference that clearly explains all this stuff?
Hi All,
I have been building a database in SQL Server Express for some months now using the Windows level authentication login that has given me full access to everything in the database.
The time has now come where I need to create user accounts and grant permissions to specific stored procedures and I'm having trouble doing this.
Can anyone give me a brief rundown of the required T-SQL commands I need to set up a user account that can do nothing but run stored procs (not the system procs which apparently are being discontinued: http://msdn2.microsoft.com/en-us/library/ms182795.aspx).
So far I have:-
CREATE LOGIN db_test_user WITH PASSWORD = 'eXaMpL3Pwd
USE db_new
CREATE USER db_test_user
The above code executes successfully but when I try and connect (using Management Studio) I get error message 'The user is not associated with a trusted SQL Server connection (error 18452)' which means little to me.
I also tried creating a 'WITHOUT LOGIN' user for the database but could not figure out how to give it a password.
I don't know (/understand) roles/schemas and don't know if I really need them as I only need user access to specific stored procs. I don't ever want them to see the tables for example so they only need the most restricted access.
Can anyone help?
Thanks,
DG
I cannot block user access to tables. I have restriced accounts at theserver, database, group, schema, and table lavel and can still opentables right up.In other words, I have absolutely no security. Any ideas?
View 1 Replies View Related
Couple of questions for the SQL Server Guru's out there.
SQLServer 2005
Web Hosting Provider
Ok I am developing a Web application in ASP.NET with AJAX, etc. etc. It will be some time before it is ready to roll out. As a mockup I created the same application in Microsoft Access and Visual Basic (VS 2008), which I can link the tables to the hosting provider on the internet. Works very well and speed is very acceptable. The want to start utilizing it with the mockup distributed app that I created.
My question is, is how secure is the data that is moving from the local application to SQLServer 2005 with the web hosting provider ? Is there anything that I can do to increase security ?
Thanks, any thoughts ?
Appreciate any suggestions or comments.
Has anyone compiled a list of out of the box features to disable (like sql mail in 2000)? Or even just a general security checklist for 2005?
I'm looking for one similar to http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec04.mspx
Hi,
I wonder if SQL Server 2005 supports row based security?
I need to set some users to see data filtered by a specific field and value...
Example: User XPTO only sees data about vendor code = '123'
Is this possible in the box?
Best Regards,
Dear mems,
I have a problem, and i don't khow how to resolve, pls help me:
My server is Windows XP (not domain),
I work with SQL Server 2005, installed on my server,
I configurate my SQL SERVER connection is "Windows Authentication mode",
I add user "MyComputerguest" to MyServerSecutityLogins to accept connections from local network
I have many databases: Db1, Db2, Db3...
But, I don't know to configurate my SQL Server to achive these:
1) My clients using "SQL Server Managment Studio" connect to Databases Db2, Db3... on my Server, they can expand, modify, add new all Tables, Sp, functions of Db2, Db3.
2) My clients are not allow to access Db1.
2) My clients can add new Databases Db4, Db5, Db6... in the future and they have full permission on every Database which they create without my interfere.
Best regard,
if you run the following script it takes access from the windows admin from getting into sql server through windows auth. The issue is that the files that are attached logging as SA after that are read only. Is there any solution? When you try and switch the file to read write sql server gives an error saying that it cant read the mdf and ldf--gives a windows access error....
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BUILTINUsers')
EXEC sp_dropsrvrolemember [BUILTINUsers], sysadmin
DENY CONNECT SQL TO [BUILTINUsers] CASCADE
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BUILTINAdministrators')
EXEC sp_dropsrvrolemember [BUILTINAdministrators], sysadmin
DENY CONNECT SQL TO [BUILTINAdministrators] CASCADE
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NT AUTHORITYSYSTEM')
EXEC sp_dropsrvrolemember [NT AUTHORITYSYSTEM], sysadmin
DENY CONNECT SQL TO [NT AUTHORITYSYSTEM] CASCADE
GO
I am not sure if this is the right place to post this but I need some help. We have an email server, with windows 2003 server, set up with SQL server 2005 and sql server management studio express which uses windows authentication. I noticed in the event viewer we keep getting an error under the application, "failure audit". about 25 a minute. The error says "login failed for user admin
The user is not associated with a trusted mysql server connection." followed by an ip address
(this username changes on a daily bases which makes me wonder if it is an attempted hack)
I have little knowledge of sql. How can I get this to stop happening. Event Viewer returns no results to help me fix this and I am having no luck researching it on my own. If there is any more info I need to provide, let me know. HELP!
Hi everyone,
I'm currently investigating the security improvements of SQL Server 2005. I've got some problems with the schemas introduced in SQL 2005 and security settings.
For my test I've created two schemas: UserManagement and Sales. A user "test" is attached to the UserManagement schema. There's a table Sales.Users containing a list of users (varchar) and a stored procedure named UserManagement.AddUser that can be executed by the UserManagement schema (GRANT EXECUTE, so "test" can execute the SP). UserManagement.AddUser simply inserts a new row into Sales.Users.
Because the Sales schema doesn't contain any user, nobody (except the sysadmin, of course) can do a INSERT/SELECT/DELETE in the Sales.Users table. As expected, the following SQL statement fails:
EXECUTE AS LOGIN='machine est';
INSERT INTO Sales.Users VALUES('Test User');
INSERT was not allowed: object 'Users', database 'test', schema 'Sales'.
The second way of inserting rows into Sales.Users is to execute the stored proc UserManagement.AddUser:
CREATE PROCEDURE [UserManagement].[AddUser]
WITH EXECUTE AS CALLER
AS
INSERT INTO Sales.Users VALUES('Test User');
The user "test" can execute this sproc without problems:
EXECUTE AS LOGIN='machine est';
EXECUTE UserManagement.AddUser;
(1 row(s) affected)To my astonishment the INSERT statement inside the stored proc does execute - although UserManagement.AddUser and Sales.Users are two different schemas. Why is that, is there a chaining happening? To my understanding SQL Server should test INSERT rights on Sales.Users for the UserManagement schema and deny the INSERT statement because UserManagement isn't allowed to INSERT in the Sales schema.
Any ideas? Help regarding the issue is greatly appreciated.
Best regards,
Alex
We have experienced an issue with back backup / restore of a database originating from SQL Server 2000 to SQL Server 2003.
We have the following setup:
SQL Server 2000
- DatabaseA
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseA
- DatabaseB
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseB
SQL Server 2005 Standard
-asdfUser is NOT Setup as a user yet.
-We restore DatabaseA and DatabaseB to the SQL Server 2005 Standard. The databases are restored with the security permissions of asdfUser being the DB Owner of DatabaseA and DatabaseB.
-We create a new SQL user named asdfUser on the SQL Server 2005 box. We then try to add the UserMapping of DBOWNER for the DatabaseA and DatabaseB. We receive an error message stating that the asdfUser already have permissions to the databases. We proceed with the user creation without those permissions.
-We proceed to the login properties of the asdfuser and view their UserMappings. The asdfUser does not have access to DatabaseA or DatabaseB. We then add the UserMapping of DBOWNER to both DatabaseA and DatabaseB. We Try to select OK and we receive an error message that states that the user already has those permissions.
-When we query the UserID's of the asdfUser that is in the database and the UserID of the asdfUser that is created, they are two different values.
I assume this is a bug... any word on a fix?
Hi,I have a ASP.net 2.0 web app which i want to run on IIS. It has a database file stored in APP_DATA folder. I have set "UserInstance" property to False, as want to access the same database file from another app, which can do its own modification. So basically i want the same database to be shared. Now after putting the web app on IIS i am getting error like :Login failed for user ''. The user is not associated with a trusted SQL Server connection.Any idea of how to solve this?
View 1 Replies View Related
If I am posting to the wrong forum, please point me in the right direction.
We have upgraded to SQL Server 2005 and Window 2003 from SQL Server 2000 and Windows 2000, and have been having all kinds of problems with security of our web applications. We have been forced to put the system account of the web server as a user in the database in order for the web applications to work. We have lost the ability to control security at the user/role level. Is this the way security is going to work in Windows 2003/SQL Server 2005? How do I use integrated security so that I can secure web pages and database objects?
Thanks
Hello,
I have created one application in visual studio 2005 and also created setup project of that application. now i want to install SQL Server Express edition with my application. so i have checked SQL Server Express 2005 in Setup Project Properities(Prerequisites...). now i want to change SQL Secirty Mode during setup. and i don't know how can we do this?
Any idia?
Hi guys , is there any ways/suggestions for strengthen up the security for SQL server 2005 ? Due to several attacks from unknown places to my database's server , so I would like to get a way for increase the SQL security. Hope able to gather some info from web as well. Thx a lot guys.
Best Regards,
Hans
We recently moved from SQL 2k to SQL 2K5 and existing application stopped working with following error message.
[DBNETLIN][ConenctionOpen (SECCreateCredentials()).]SSL Security error.
The code used to make connection is as below.
Option Explicit
Const NewConnStr = "Provider=SQLOLEDB;Persistant Security=False;User ID=%DBUser;Password=%DBPassword;Initial Catalog=%DBName;Data Source=%DBServer,%DBPort;Network Library=dbmssocn"
Dim oConn
Dim sConnStr
Dim sServer
sServer = "SQLSERV01"
Set oConn = CreateObject("ADODB.Connection")
On Error Resume next
sConnStr = Replace(Replace(Replace(Replace(Replace(NewConnStr, "%DBUser", "test1"), "%DBPassword", "test1"), "%DBName", "model"), "%DBServer", sServer), "%DBPort", "1433")
Call oConn.Open(sConnStr)
This script runs under stripped-down Windows enviornment (Windows PE) with very basic components (i.e. no GUI, only command-line interface). I am trying to find out if there is any client side setting to bypass "self-generated SSL ceritificate" on server. I have tried all things on server side from disabling shared memory protocol, setting "Force Encryption" property etc. Could somebody help me with name of this property?
Also, is it possible to not use self-generate certificate on server. We don't want any kind of encryption for SQL connections.
Thanks..
It appears that I have a machine where the SQL Server 2005 Security groups were deleted, now I am unable to change account settings in configuration manager ( I get a WMI provider error ). Is there any way to recreate these security groups without uninstalling and reinstalling SQL Server?
View 3 Replies View Related