I 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?
Hello.
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.
Hello!
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.
Rayd 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."
Hi 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
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
I 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
Hi 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
Hi,
How can we restrict the copying of MDF and Log Files.
Thanks
Avi
Hello,
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
Hello,
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
Problem description
There are two sql servers with the same database in different locations with replication running on them. Users in the two locations insert or update date on there own database through an ms access application . The data are replicated to both servers.
Someone wanted a copy of the database and they dropped replication, but users continue to modify and add data on both servers and so replication could not be back again without merging the two databases as one again and restarting replication.
My job was to merge the databases and make replication work and after allot of work all is fine.
I copy and attached the database on my pc that I set up as a server and did the job. Then I went back on both servers that had replication running before I attached the database and set the subscriber and publisher and all was fine to that point.
The database has six groups with permissions on them and many database users on each location. Both the groups and the users show on the database.
I create a new login and user and I add him to the groups to get the permissions.
Now back on a user pc I login with the new user name run the ms access application and try to add some new records, but I get errors that I cannot insert null values to two of the fields in several tables in the database.
This was not happening before and the database was recording the values in those fields namely the user name and the date that the new record was created.
After some reading I found out that I might need to set the groups and permissions on the views and tables from the beging but Im not an expert.
Any ideas?
Thanks allot
George
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database.
I'm not sure the best way to do this.......
Can anyone enlighten me?.....
I am working on a project that was written in Visual Studio 2003 using asp.net and C#. The project accesses a SQL Server 2000 database. I need to test the application. What I want to do is to copy the database over to a test server. The problem is that the test server only has SQL Server 2005 on it. Would it be a problem if I backed up the database using SQL Server 2000 and then recreated it on the server as a SQL server 2005 project? Would my source code accept the new database even though it would now be in sql server 2005?
Hi, 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.
We 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?
Hi 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.
Morning 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.
Dear 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
can we secure mdf file, if it's copied from one location to anothercould not be used ???*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI have a situation where I have an app that uses a sql server (msde)database. The app will be used in environments where no one should beable to manipulate the data except the developers (app admins) - noteven site database admins. When the application and msde is installed,a default instance of the database gets attached to msde or built byscript. by default, a built in server acct and approle acct exist tosecure the data accordingly with passwords concealed. What can be doneto keep someone from copying the mdf and ldf files to another machinewhere they have admin rights and manipulating data?Thanks.
View 1 Replies View RelatedHi,
I want to make some steps towards securing production database.
1. Give limited rights to Developers, i.e. db reaonly, db writedeny
2. Make strong password for local and Domain
3. Use Windows authentication
4. Enable log for 'Failed Login' attempts.
What steps I need to take in addition to those?
Hi,
I€™m trying to secure my SQL Server 2005 infrastructure, and I€™m seeing that some sites are recommending that certain extended procedures be restricted to sysadmin only.
http://www.sqlsecurity.com/FAQs/SQLSecurityChecklist/tabid/57/Default.aspx
This site recommended securing the following extended procedures:
Extended Procedurs:sp_sdidebug xp_availablemedia xp_cmdshell
xp_deletemail xp_dirtree xp_dropwebtask
xp_dsninfo xp_enumdsn xp_enumerrorlogs
xp_enumgroups xp_enumqueuedtasks xp_eventlog
xp_findnextmsg xp_fixeddrives xp_getfiledetails
xp_getnetname xp_grantlogin xp_logevent
xp_loginconfig xp_logininfo xp_makewebtask
xp_msver xp_perfend xp_perfmonitor
xp_perfsample xp_perfstart xp_readerrorlog
xp_readmail xp_regread xp_revokelogin
xp_runweb
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3184075&SiteID=1
This thread recommended (implicitly) securing the following extended procedures:
Extended Procedures:sp_OACreate sp_OADestroy sp_OAGetErrorInfo sp_OAGetProperty
sp_OAMethod sp_OASetProperty sp_OAStop sp_sdidebug
xp_availablemedia xp_cmdshell xp_deletemail xp_dirtree
xp_dropwebtask xp_dsninfo xp_enumdsn xp_enumerrorlogs
xp_enumgroups xp_enumqueuedtasks xp_eventlog xp_findnextmsg
xp_fixeddrives xp_getfiledetails xp_getnetname xp_grantlogin
xp_logevent xp_loginconfig xp_logininfo xp_regread
xp_perfend xp_perfmonitor xp_perfsample xp_perfstart
xp_readerrorlog xp_readmail xp_revokelogin xp_runwebtask
xp_schedulersignal xp_sendmail xp_servicecontrol xp_snmp_getstate
xp_snmp_raisetrap xp_sprintf xp_sqlinventory xp_sqlregister
xp_sqltrace xp_sscanf xp_startmail xp_stopmail
xp_subdirs xp_unc_to_drive xp_dirtree
Looking at these lists, I can see they might have missed other extended procedures like xp_regwrite, xp_regdeletekey, and xp_regdeletevalue.
My questions are: Is there any way I can find an exhaustive list as to what extended procedures should be restricted? Is there a website/Microsoft resource that can help me identify what to restrict?
Any other information you can point me to to secure our infrastructure would be appreciated.
Hye 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..
Hi,
I'm without a clue when it comes to SQL and how to secure it!
I've set up a SQLExpress running on a dedicated server on the web and I'm using TCP/IP remote connection to connect to the DB from a the web server running the ASP .
Would it be better using named pipes?
Also is there some way I can additionally authenticate a connection based on IP numbers?
I would really appreciate some advice thanks.
Hey all,
Trying to add some security to what I'm learning I realized my querystring was vulnerable so I started looking through the threads on how to secure it. I've seen a few things, but in general the folks working on it are too advanced for me and are doing more with their query than my simple query. I'm looking for a little help of course :-)
So, my original querystring was pretty basic of course
Dim querystring1 As String = Request.QueryString("topic_id")SqlDataSource1.SelectCommand = "select * from msg_msgs INNER JOIN users on msg_id_user = users.user_ID where msg_topic_id = " & querystring1
And my first attempt at securing it didn't wind up having the @ sign, so I don't think it secured it. It also brought back every record in the DB
Dim querystring1 As Parameter = New Parameter("mylink", TypeCode.String, Request.QueryString("topic_id"))SqlDataSource1.SelectParameters.Add(querystring1)
And finally, after reading a bit I wound up with this
Dim queryStringId As String = Request.QueryString("topic_id")Dim id As IntegerIf Int32.TryParse(queryStringId, id) = True Then Dim idParam As New SqlParameter("@id", id) Dim objCmd As New SqlCommand("SELECT * FROM msg_msgs where msg_topic_id = @id") objCmd.Parameters.Add(idParam) SqlDataSource1.SelectCommand = (objCmd.ToString)Else Response.Redirect("./threads.aspx")End If
I guess the worst part is that I know I need the @variable piece, though not why nor how to add it, and all my searches on parameterized queries are loosing me because the folks writting are so far beyond where I am at this time. I would really appreciate it if someone could not only help me get the code working, but also understand the parts of it. More often than not I find myself mimicking someone elses code and then knowing how to do it, but not why it works. Thanks
Hi,I am writing an application that uses MSDE to store data.Both application & MSDE run on the same computer.I want to regulate the operations done on the DB by the user. Forexample, I don't want to allow "standard" users to delete records,update certain fields, etc...I can regulate these rules within my program, but what if the user runsMSDE query for example on the DB and fetches the list of users &passwords from the DB ?In other words, I need to make sure only the application has access tothe DB. This seems like a common type of problem but I haven't beenable to find any solutions.Any suggestions would be greatly appreciated.Danny
View 1 Replies View RelatedHi all ,
There is a table named "Users" in my SQL Server DB , there is password field that I'm going to make it secure , Now I explain you How I plan to this :
There are two columns named "SID1" & "SID2" other than "Password" Field in this table and their datatype are all VarBinary ,
When I want to add user to this table , two random byte arrays are generated using RNGCryptoServiceProvider Class , then I combine SID1,SID2 and Password byte arrays to generate password , after that this byte array is Hashed using
SHA384 Class and is stored into database , Also two randomly generated byte arrays are stored in SID1 & SID2 Fields , For Comparison , I do all above steps reversely , Up to here there is no problem and everything work OK , but I have two questions ,
1) Is this way of storing password secure?
2)Isn't it better to put these codes into a CLR Integrated Class and do all above steps in SQL-Server-Side?If Yes , I should send my clear-text password to sql server For Hashing and Validating and this password could be stolen using a packet sniffer , What Should I do in this Step? Installing SSL?but I'm in a workgroup environment , How can I Install SSL in workgroup environment? Isn't there any other solution?
Thanks in advance for your great helps ,
Cheers
Hi,
I am having a problem connecting my .net applications from the application server to the database server. When I run the application from my windows xp (sp2) box it works fine. When I try to connect via SQL Management Studio to the database server from the application server I get the same error.
Here is the error:
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Here is the Environment:
App Server:
Windows Server 2003 Standard Edition
Inside Company's Firewall/ Network
Database Server:
Windows Server 2000 Advanced Edition
SQL Server 2000 SP4
Remote Connections to the Server is checked
Enable Protocols: Named Pipes & TCP/IP
TCP/IP Port: 1402 (I don't know why it isn't the default of 1433)
The db server is sitting out side the Company's firewall (don't ask me why). I can access it fine from inside the firewall on my XP box but not from windows server 2003. There is a web server outside the our network that also connects to the db server with no problem and that is running Windows Server 2003 Web Edition.
I can ping the db server from the app server using the IP address.
I tried using the IP address and the port 1402 in my connection string but that didn't work from any machine (XP and Server).
I imagine the issue is somehow related to the company's firewall but why would it only block Windows Server 2003 and not XP?
What do I tell the network admin to change?
Any help would be appreciated.
Thanks,
Oran
When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
How do I solve this problem?
I have a database called 'DB1' in SQL Server 2000. I want to create the same database in SQL Server 2005 Express including the original data in tables.
How would I do that? I cannot find any option to do this upgrade in SQL Server Management Studio.
Hi All
I have Computer is setuped SQL Server 2000 and SQL server 2005 when I restore SQL Server 2005 it's OK but when it's not OK when I restore in SQL Server 2000. The Error as follows:
Please help me to solve this problem. Thanks
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?