Maintaining Security

I am a beginer in SQL Server. I have developed a simple accounting application in VB and SQL. Now I have successfully completed my application. Now I want to deploy it to my client. So I installed SQl Server and required VB components in the clients computer. I also created 'sa' login and secret password only know by me. I thought my data in that clients computer was full safe but later on i found that we can also connect to the sql server using the NT administrative account and easily change the data of the database. So now I am worried that if someone enters and access the clients computer with administrator's password then he/she can change my data resulting the corruption of the data. So is there any way that I can prevent the access the database to the client with the NT administrative account or any way 2 track the way the data changed?

I am probably missing something from your application description; please correct me if any of my assumptions is wrong. I will list a few assumptions based on my understanding and try to answer your question and you some recommendations based on them:

* There is one SQL Server 2005 machine that can be remotely access by multiple clients running your application.

* You have a hardcoded SA password embedded in your application

I want to start by pointing out that by default members of the Windows Administrators group on the machine running SQL Server will have access to via this membership. You can remove such privilege to prevent an accidental access by removing the BUILTIN\Administrators login from SQL Server: DROP LOGIN [BUILTIN\Administrators]

It is important to remark that this will prevent the Windows administrators from connecting to SQL Server 2005 when the server is running normally, but any local administrator will be able to connect to SQL Server 2005 by starting the server in single-user-mode, this is allowed for system maintenance and to prevent accidental lockouts from the system. It is also important to note that it is pretty much impossible to stop an adversary that already has full access to the SQL Server 2005 machine as system administrator.

The next thing to note, and that hopefully is something I misunderstood is the hardcoded SA password in your application. I strongly recommend against such practice as an adversary can easily recover the plaintext password from your client and compromise the data. Even more, the account you are using in this case is SA; I strongly recommend using the “least privilege principle” and define roles for your application, based on the most common tasks. Probably you have administrative tasks (create users, tables, etc.), read-write tasks (update, inserts, etc.) and read-only tasks (search and read data, but not write). I would recommend creating different principals for such tasks and grant only the required privileges for each principal type, restricting access to the most privileged accounts (such as SA) only to users that require such high privileges.

Additionally I want to remind you of a new feature in SQL Server 2005 that may affect your design in case you use hardcoded passwords: password policy. SQL Server 2005 running on Windows 2003 can take advantage of the Windows password policies and use them in SQL logins (including SA); these password policies allows a domain/machine administrator to define password complexity, life-time, lockout policies, etc. in a centralized way. When running on Windows 2003, SQL Server will use this new feature by default, but you can explicitly turn it off for any given SQL account. It is highly recommended to take advantage of this feature especially for SA, but if you have hardcoded passwords (or passwords that are difficult to update in your app) you will probably have to disable the password policy for the accounts you use in your app.

Detect unexpected value changes is a little tricky. One possibility here may be to use a certificate to sign the data and store the signature along with the data; this way your application can verify that the signature is valid before consuming the data.

I hope this information will be useful, let us know if you have further questions or/and feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||Thanks. As I am only concern abt the protection of my data I wanna suggestion on "how do I safe gaurd my data" so only the login used by me or my program can change/add/remove the data from my database not other users from any other program. And I also wanna know that whether can I track the activities of third user who changes the data in my database.|||

You might want to take a look at a thread that is related to your question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1.

You can only achieve what you are asking if you have full control over the machine where your program is installed. Otherwise, you could write your program, so you could detect if someone changed the data, but you would not be able to prevent such changes. In other words, there is no way to prevent corruption of your data by a third party.

Thanks
Laurentiu