Securing Data And Log Files In SQL Server
Mar 31, 2008
Hi,
How can we restrict the copying of MDF and Log Files.
Thanks
Avi
ADVERTISEMENT
Securing The Data Of Tables
Jan 22, 2006Hye guys,
I am not the perfect database designer nor the programmer. I have designed and developed a simple database application which uses VB as frontedt and SQL as backend. My Program worked fine.. Now I have 2 deploy it in clients computer where DBA is another person by which I am worried abt the data in the table. As X person is a DBA there he can easily change data of my tables in the database.
So I want an easy way by which the X person can't edit the data of the tables of my database only I can change the contents of my tables but i should be able 2 change the data from my program only..
Plz Help..
SQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups
Jan 9, 2015I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View RelatedSecuring SQL Server
Jun 7, 2006Rayd Abdou writes "hi all, i have an SQL server at my home and i think i got hacked from it :( and i really want to know what to do to secure the SQL Server from, disable permissions ?
what commands ?
Thanks for helping me..
Rayd."
Securing An SQL Server
Apr 18, 2007Hi all,
I have been given a task of securing an SQL server 2005 that is currently open to SQL injection attacks. I have identified 3 main areas that I need to secure, these being:
1.Different SQL server logins - currently all database work from the site is performed using the sa account (don't ask me why they've left it so open to attack, I've not long started here!)
2.Custom error pages - to reduce feedback to a potential attacker on the database structure
3.Query Validation - any dynamically generated queries will be passed through a validator in order to possibly strip out any commands that we identify as those that an attacker would attempt to pass via the url.
Obviously, point number one is the big one. Based on this, my question is, what are the series of steps I would need to go through in order to;
a) setup a user login that has read access to many of the database tables (and execute access to some of them)
b) setup a user login that has read/write/update/execute access to other tables and stored procedures
I have read a lot about schema's, but I haven't had that many dealings with SQL server 2005 (yet), and haven't been able to find a step-by-step guide to setting up a schema/users and assigning permissions to them.
If someone could point me in the right direction of an "idiots guide to", that would be great, or if theres anyone that could list the steps I need to perform, that would be even better.
Also, if anyone has any other suggestions about how i could secure the server, I am all ears.
Thanks in advance,
Paul
Securing SQL Server
Mar 18, 2008
Hi all,
What is the best way to keep the data secure in my SQL Server 2005? and what is the best way to secure the communication between the client application and SQL Server 2005?
Thanks,
Shyam
Securing A Remote Server
Sep 27, 2007I have what some might consider a dumb question but I really don't know the answer.
Until recently all our .Net work ahs been hosted on our internal network and the Sql Server (2000) was not open to the outside. However recently our company is looking at hosting other outside SQL Server applications that require users across the country to connect directly to our SQL Server (not through an ASP.Net app).
The concerns we have is that ASP.Net runs on the NETWORK SERVICE account. If a user outside our network were to know the IP and name of onw of our databases could they connect with ASP.Net using a Trusted Connection or do trusted connections only work if the application is hosted on the same network?
One of the applications we are looking at hosting is showing a list of all databases on our server (I did find the article on modifying sp_MSdbuseraccess but that didn't seem to work) so if someone got a hold of this list would they be able to connect?
Thanks
Securing Microsoft SQL Server
Mar 9, 1999Hi all,
Does anyone know were to find any articles/information on how to Secure Microsoft SQL 6.5 Server? . Apart from SQL online books
Panchal
Securing SQL Server 2000 Database?
Feb 20, 2005I want to know how I can protect my SQL Server database. SQL Server 2000 does not have Database Encryption feature and using only Authentication is not a fool-proof solution, as far as stand-alone desktop application is concerned.
Does password protection of SQL Server 2000 database really works when you have all types of cracking tools widely available on the net?
Securing SQL Server 2005 Express
Mar 19, 2008Hello,
I developed a small desktop system using SQL Server 2005 Express as the data backend. The machine that hosts SQL Server 2005 Express, as well as all the workstations that interact with it, belong to the same private LAN segment. All of them have non-public IP addresses from class 192.168.0... which means they're not directly visible from the outside world, despite having internet access through a router that does NAT. As we all know this is the typical scenario for sharing internet access in a small LAN.
Now, my concern is with the security of the host running SQL Server 2005 Express. In particular, what measures do I have to implement to minimize the risk that may come from the public internet ? It's naive to think that because the potential attack surface is NATted behind a router, security is guaranteed and data theft or DoS attacks won't simply happen.
What are the guidelines for securing SQL Server 2005 Express in a scenario like this ?
(Side note: SQL Server 2005 Express is running on Windows XP Pro)
Thanks.
Fernando
Securing SQL Server Tables Linked Via Access
Jun 7, 2000Hello,
We are currently live with a CRM solution (Siebel) that uses SQL Server 6.5 as the back end. All is fine and dandy, except I have some reservations about security.
Quite simply, it is possible for anyone to open up MS Access and link to any of the SQL Server database tables via the ODBC DSN used by the Siebel front end. This DSN is necessary for Siebel to function.
I am bit worried that someone (out of incompetence or spite) might do just that and cause some serious damage. Its probably technically beyond the large proportion of our users (especially those that could make mistakes!), but I can't get the nagging fear out of my head.
Does anyone know of anyway to combat this problem? I have scoured the web, including this site, and cant seem to get any information on this.
Thanks and Regards
Dike
Securing A Database When The User Logs Onto The SQL Server.
Oct 4, 2007Hello.
Not an SQL admin. We have an SQL 2005 server that has about 5 DB's on it. One database is maintained primarily by a third party. Often when they need to do upgrades they login remotely to the desktop of our SQL server. Is there a way to apply permissions to specific databases like you would for NTFS? That way they can only backup their database and not do anything to any other databases? Thanks.
Securing The MS SQL Server 2005 Database On Client Side
Apr 27, 2006Hello!
I developed database driven .NET application and I need to deploy it. I faced a problem, which is "how to protect my database against direct access". I use MS SQL Server 2005 Express Edition as a DBMS and appropriate database.
I want to make possible to manipulate with data in my database only through my client application.
1. How do I define SA password and instance name in silent mode of MS SQL 2005 EE installation with Mixed type of Authentication?
1.1. Can I change SA password after the installation?
2. If my database be attached to my new instance... Is it possible to copy my database, attach it to another instance and get a direct access to its objects?
Is there solution that make impossible to connect to my database on third-partie's side Idirectly, without using the client application?
I appriciate for a help.
How Do I Recover Data From SQL Server's Log Files?
Nov 29, 2006Dear All,One of our employee made a mistake and deleted something from database.I would like to recover that log file without restoring the backup andthe other log files. The reason I want to do this is our database isgetting real time information I cannot shut down the database. Is thereany way to do this please let me know and help me .Kindly regardsLS
View 2 Replies View RelatedImport Data From Text Files Into SQL Server...?
Jun 6, 2005Hi,i wanna develop an web-database application with ASP.NET,C#, SQL server 2000.i already have some data whichs been in text format(text file) and now, i want to import the same into my database.the problem is, the text file has got many line breaks and also its not well formated to import it using DTS.Can any one help me out in importing the same.thanks in advance
View 3 Replies View RelatedData Files Not Located On SQL2000 Server
May 6, 2002My company recently purchase a NetApp FS870 and we want to move the SQL data files for out production DB. The NetApp is a NAS. Without loggining in to the SQL Server and establishing a drive mapping.
Does anyone know how to connect SQL2K to the remote machine?
We would prefer to use UNC naming to access the share, as leaving servers logged in and open with a mapped drive is a huge secutrity hole, and presents a reliability issue too.
Thanks
Greg
SQL Server 2014 :: Insert Data From PDF Files
Mar 23, 2014We are facing a problem with loading data from .pdf files from vendor..pdf files have data in tabular format and we would like to insert those fields into a SQL table.We do not want to insert the physical location of the file but, we need to insert the data within the file.How can we read a pdf file?
View 6 Replies View RelatedMoving Data Files To A New Disk, Same Server
Jul 23, 2005Hello, I have been having a bit of trouble finding help on the safestway to move data files to a different disk on the same server. Mosthelp is about moving data files to a different sqlserver. I just wantto move the files to a different drive on the same server. Any helpwould be appreciated.Thanks,David
View 6 Replies View RelatedData Migration Into SQL Server 2005 From Xml Files
Mar 6, 2007Friends
Any one of you please provide step by step info for migrating sql server 2005 from xml files.
cheers :)
satish
Import Text Files Data Into SQL Server
Feb 17, 2007I need to extract data from text files (around 200) and import into sql server tables. I tried using SSIS foreach loop container but could not manage it. Can anyone guide me how this can be done?
All help appreciated.
Thanks,
DTS: Copying Data From Text Files To A SQL Server Table
Sep 10, 2001Hi all,
I got a situation here.....
From a source table (in SERVER1) I get ids of candidates and from another source (in SERVER2) I get their CVs (text files stored in various Folders). My destination table (in SERVER3) has two fields, CandidateId & CandidateCV.
I have to transfer the data in above fashion for nearly 1 million records.
How can I write a DTS package which picks up the text file from SERVER2 based on the CandidateId which comes from SERVER1? Probably I need some kind of looping mechanism which changes the candidate id & his CV file.
Can anyone help???
Thanks...
Exporting Databse Table Data In SQL Server 6.5 Into Text Files
Mar 11, 1999Hi,
I have to export the table data from my databse into text files as I nedd to put it in Informix database using a sheel script. Is there a way by which I can do this.
Is there any other way by which I can put the data from SQL Server to Informix.
Any takers,
Thanking you in advance.
Bye for now,
Himauhu
SQL 2012 :: How Server Fills Empty Spaces In Data Files
Oct 1, 2015I understand that we shouldn't shrink data files as it might cause heavy fragmentation along with log usage, high IO/CPU etc.
In a DB in which lot of DML transaction occur, there will be empty spaces whenever deletions occur.
Will SQL Server fill that part with data when new insertions occur ?.
Linked Server To Text Files: Is Possible To Detect Changes Made To Those Files? (SQL Server 2005)
Sep 3, 2007Hi gurus,
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
Thanks a lot in advance!
SQL Server 2012 :: Where To Find List Of Values For Event Data Files
Jan 21, 2015Got following query:
SELECT
event_data.value('(event/data/value)[4]', 'bigint') AS cpu_time,
--database name
event_data.value('(event/data/value)[5]', 'bigint') AS duration,
--estimated cost
--estimated rows
--nest level
[code]...
Basically, is a simple T-SQL query that reads the local file for my already setup extended event sessions. But I can't find the way to retrieve the following attributes as part as the T-SQL query:
--database name
--estimated cost
--estimated rows
--nest level
--object name
I am trying to find a BOL or some MS link with the full list of possible values for event_data.value but can't find one.
SQL Server Admin 2014 :: Inconsistent Data In Database And Master Files?
Apr 21, 2015USE <database>
select * from sys.database_files
and
select * from sys.master_files where database_id= <db id>
give me different size of memory optimized file in <database>
Microsoft SQL Server 2014 - 12.0.2456.0 (X64)
Doing A Data Import Using DTS Wizard In SQL Server 2005 - Being Efficient With 5 Flat Files
Apr 13, 2006Hi,
I'm a new user of SQL Server 2005. I have the full version installed. I also have SQL Server Business Integration Dev Studio installed. My OS is Windows XP.
I'm importing a series of 5 flat files into a database on one of the SQL Servers we have. My goal is to get 5 different tables (though perhaps I should do one and add an extra field to distinguish each import) into the database for further analysis.
I tried doing an import via DTS Wizard. There are no column names in the flat file so I defined them during the import process (all 58 of them). When I got to the end, I had an option to save the import process as a SSIS (SQL Server Integration Service) Package on:
SQL SERVER (I don't have permission for this)
or
FILE SYSTEM (did this one)
I saved the Package locally in hopes of being able to go back in, change the source file and destination table of the package and quickly get the other 4 flat files imported.
My problems are:
1) I couldn't find how to run the *.DTSX Package file to run in SQL Server Studio (basically reuse the Package with minor changes and saving me having to redefine the same 58 columns on each flat file import)
2) Tried but didn't understand how to run it in SQL Server Bus Intel Dev Studio (i.e. understanding the mapping and getting the data types right so it wouldn't error out)
3) Don't know how to make the necessary changes so that the Package handles the next source file and puts in a new destination table (do I need to do 5 CREATE TABLES so this Package has a place to run to?)
4) Does the Package need to be part of a Project to run (I haven't found how to take an existing Package and make it part of a Project/Solution)?
5) Is there a good book or online resource for just getting the basics of using SQL Server 2005 and SQL Server Business Intelligence Development Studio?
I'm really at a loss after spending a day fruitlessly on it scouring the help files, forums and experimenting around.
Hope somebody can point me in the right direction.
Regards,
Patrick Briggs,
Pasadena, CA
Doing A Data Import Using DTS Wizard In SQL Server 2005 - Being Efficient With 5 Flat Files
Apr 18, 2006I just spent some time working out how to do a seemingly simple task. I€™m sharing the steps I took to do this in hopes it saves other SQL Server 2005 users (especially newbies like myself) time.
My original question posed on several SQL newsgroups was based on this goal:
I'm importing a series of 5 flat files (all with same file layout) into a database on one of the SQL Servers we have using SQL Server 2005 (SQL Server Management Studio) . My goal is to get 5 different tables. I want to do this without having to redo all the layout criteria 4 additional times.
Below are the steps I followed to get a solution (all done in Microsoft SQL Server Management Studio):
Create the Package (data import)
1) Use the SQL Server Import Export Wizard (equivalent to SQL Server 2000 Data Transfer Wizard) to import your first flat file. At the CHOOSE DATA SOURCE window browse for your file.
2) Under the Advanced tab, you can set your Column attributes (€œoutput column width€? or €œdata type€? to name a few). I highlighted all the columns and selected €œstring [DT_STR]€? for data type. To avoid truncation errors, I selected 255 for output column width. You can name the columns whose data you are most concerned with (I did import all the available fields).
3) After choosing a server destination you will have a €œSELECT SOURCE TABLES AND VIEWS€? window pop up. Under the €œMapping€? column you can choose to tweak your mapping further editing in SQL (see Edit SQL button). I didn€™t.
4) The €œSAVE AND EXECUTE PACKAGE€? will pop up. The €œExecute Immediately€? box should be checked and you should check the €œSave SSIS Package€? (SQL Server Integration Services). When you do, select €œFile System€? for where to save this import-file-package to.
5) Click OKAY for the Package Protection Level and the €œSAVE SSIS PACKAGE€? window will appear. Browse for a path on your local computer to save to.
Modify Package (data import) for Next Use
6) In SQL Server Management Studio, browse for the Package and open it.
Preparation for SQL Task €“ box
7) You should see a screen that shows two boxes (€œPreparation for SQL Task€?) and (€œData Flow Task€?).
8) Right click on the former and select €œEdit€?.
9) On the €œSQL Statement€? row, click into the right column and select the €œ€¦€? box
10) Change the destination table (the table you will create with this package) to a meaningful name and click OK.
11) Click OK for the €œSQL Task Editor€?
Data Flow Task - box
12) Right click on the €œData Flow Task€? box and select €œEdit€?.
13) Three boxes will appear €œSourceConnectionFlatFile€?, €œData Conversion 1€?, and €œDestination - <whatever table name your original data import went to>€?. Below them is a section that displays €œConnection Managers€?
SourceConnectionFlatFile - editing
14) The first thing you will want to do is change the import source to a new flat file. You do this by going below the boxes under the €œConnection Managers€? window and right clicking on €œSourceConnectionFlatFile€? and then selecting €œEdit€?
15) Browse for the new €œFile Name€? and select it.
16) A €œMicrosoft SQL Server Management Studio€? window will pop up asking you if you want to €œkeep or reset the existing metadata€?. The metadata is just your column definitions and choosing €œYES€? to keep this makes sense if you are doing data imports on files with the same file layout.
17) Still in the €œFlat File Connection Manager Editor€? window, change the €œConnection Manager Name€? to something meaningful (I add <_> at the end and then the name of the table the flat file is going to) and click OK.
SourceConnectionFlatFile €“ box (editing)
18) Right click on the €œSourceConnectionFlatFile€? box and select €œEdit€?.
19) Your newly named €œFlat File Connection Manager€? should appear in select box.
20) Click OK, right click again on the €œSourceConnectionFlatFile€? box and select €œShow Advanced Editor€?.
21) Under the €œConnections Manager€? tab, your newly named €œFlat File Connection€? should appear (the prior step is necessary for the advanced editor to recognize your change).
22) Under the €œComponent Properties€? tab, on the €œName€? row, click into the right column and rename to something meaningful (notice the €œIdentification String€? row description changes too once you click out of the €œName€? row)
23) Under the €œColumn Mappings€? tab, just confirm you are mapping your flat file fields (€œAvailable External Columns€?) to a destination table€™s fields (€œAvailable Output Columns€?).
24) Under the €œInput and Output Properties€? tab you can check in €œFlat File Source Output€? to make modifications to either your €œExternal Columns€? or your €œOutput Columns€? €“ you shouldn€™t need to for a simple import.
((NOTE: any changes you make here would likely need to be consistent with the column properties found under the €œConnection Manager Window€? for the €œSourceConnectionFlatFile€? as well as the €œData Conversion 1€? box under the €œData Flow Tasks€? window, so exercise caution
25) NOTE: This process has worked for me by making my source columns all €œstring [DT_STR]€? data type and the output columns all €œUnicode String [DT_WSTR]€? data type.
Data Conversion 1 €“ box (editing)
26) There is nothing you need to do here. By right clicking on the €œData Conversion 1€? box and selecting €œEdit€?, you can see and change the data type of the output columns (the ones in the table your importing the flat file to). There are probably more edits one can do but they€™re beyond what I€™ve learned.
Destination - <whatever table name your original data import went to> €“ box (editing)
27) Right click on the €œDestination - <whatever table name your original data import went to>€? box and select €œShow Advanced Editor€?.
28) Select the €œComponent Properties€? tab.
29) Select the right column at the €œName€? row and change the name to something meaningful (ie. related to the source file name or the table name you€™re importing to).
30) Select the right column at the €œIdentification String€? row and it will update to this change.
31) Select the right column at the €œOpenRowSet€? and change it to the name of the table you are importing your flat file to (this should be consistent with table name under step 10).
32) Click OK
33) Select FILE and select €œSave As€¦€? and then give your package a new name that€™s meaningful (this will be helpful if you have to rerun the import of the flat file later).
Run (execute) the Revised Package (data import)
34) Go back to SQL Server Management Studio and open the Object Explorer
35) Connect to an €œIntegration Services€? component. This should essentially be a local instance (not sure where it is on the local computer or in SQL Server Management Studio on the local computer).
36) In €œObject Explorer€? go down to your €œIntegration Services€? object and expand it.
37) Expand €œStored Packages€?
38) Right click on €œFile System€? and select €œImport Package€? and an €œIMPORT PACKAGE€? window will appear
39) For €œPackage Location€? choose €œFile System€? and then browse for the €œPackage Path€?
40) Click into the €œPackage Name€? and it defaults to your Package€™s file name.
41) Click OK and the Package is imported.
42) Right click on the newly imported Package and select €œRun Package€?
43) An €œExecute Package Utility€? window appears
44) Select €œExecute€? and the package runs.
Securing Web Application
Dec 11, 2007Hi, I am using ASP.Net 2005 with C# language and SQL SERVER 2005...
I am developing an web based application and have to deploy it on server.
I need to prevent my site from the SQL Injection and have to use some algorithms.
What is the best technique or method (Algorithm) in .Net ?
Give some measures to prevent from Hackers.
Securing MSDE Db
Jun 23, 2005We are looking for a way to tightly secure the database of a product
being developed in MSDE 2k & C# so that even the db design
cannot be viewed or data retrieved through any migration tools.
The NetLib database security tool perfectly matches our requirement but
is overpriced. Any suggestions on the next best alternative?
Securing The Master Db
Jan 16, 2001Hi All,
I am currently creating a SQLServer 7 server. This server will be used to host customer databases that I will restore on to the server. However, I want to prevent these customers accessing any other databases on the server, apart from their own. By removing the public database role from each customer database, and granting them very limited rights (basically exec rights on their own Stored Procs)on their own db, I plan to limit them to their own db. However, my problem is this:
As you cannot remove the public role from the master db, a user could easily exec the following in a stored proc to read from the master:
Select * from master..sysusers
How do I prevent the users from accessing the master in this fashion.
Will removing every permission from the public role in master be enough?
Will removing every permission from the public role in master have any other side effects?
Will removing the public role from other user dbs be enough to secure them?
Any suggestions/pointers would be appreciated.
Gary.
Securing DTS Packages From
May 8, 2007Morning Guys,
I'm trying to figure out a way of securing a DTS package and understanding how it works more and more.
I have system administrators that have accesss to sql server.
As dbas here we work with dts packages. We would like our packages secured from the system administrators that want to poke around with our work.
how would we lock our objects down without messing them up from executing.
The packages have been created under the servernameAdministrator.
servernameAdministrator is the owner of the package.
What would be the best way to start to understand all this.
1). Using an owner password a user password
2). Denying access to the sp_add_dtspackage & sp_get_dtspackages...
3). When generating a DTS RUN util to make a job using the dts package
usually the password is embedded in the string even after encrypting the pacakage in clear text....
any suggestions to lead me in the right direction......
jonathan
If you have an owner password with no user password, you cannot execute the package without the owner password. Click OK to continue saving.
Securing MDF File
Jul 31, 2007Dear All,
I have developed a application using SQL express.
One of my client wats to protect his database so that if some body takes the backup he/she is not able to view data either directly or from the application i am delivering (may be he can buy my software and use his database or simply use demo version of my data)
Previously I used Access database and use database password protection (which every body knows is not good enough).
Now what I should do to protect my database (I am not worried about database structure or other objects but clients data that he will enter into the software like accounts data)
I need a moderate and a hard solution so that depending upon clients ability to affort I can implement at client side. There is no need to deliver protection in distrbution of my software.
Thanks in advance
MANOJ JAIN