Showing posts with label deployed. Show all posts
Showing posts with label deployed. Show all posts

Thursday, March 22, 2012

Database Backup Security

Hi

We have developed and deployed a database which contanis very sensitive
information. Our client is now concerned about the security of the back
ups. In essense, if someone in the organization can get hold of the
backup of the database, he can simply restore it on any sql server in
the world with sa permission.

I know Microsoft provides flexibility of adding the "Password" in the
Backup t-sql statement but it wouldn't be of much use if the back up
task is saved as a script and password will be written inside the
script.

your suggestions are really appreciated!

Thanks<muzamil@.hotmail.com> wrote in message
news:1115055348.283887.64500@.z14g2000cwz.googlegro ups.com...
> Hi
> We have developed and deployed a database which contanis very sensitive
> information. Our client is now concerned about the security of the back
> ups. In essense, if someone in the organization can get hold of the
> backup of the database, he can simply restore it on any sql server in
> the world with sa permission.
> I know Microsoft provides flexibility of adding the "Password" in the
> Backup t-sql statement but it wouldn't be of much use if the back up
> task is saved as a script and password will be written inside the
> script.
> your suggestions are really appreciated!
> Thanks

If your client believes they cannot trust their own IT staff and/or cannot
secure their own backups, then I would suggest they have a number of serious
issues to address. On the technical side, they can implement a few standard
practices such as backing up to NTFS drives with appropriate ACLs, limiting
physical access to backup drives and tapes to a minimum number of trusted
staff, using OS-level auditing to see who accesses the files etc.
Ultimately, though, someone has to have access to backups, domain admin and
Exchange admin accounts etc., so they need to have people they can rely on,
and that probably isn't a problem you can or should try to solve for them -
it's really a human resources issue.

However, I appreciate that in reality, and especially in smaller companies,
these things may be not always be so easy. One possibility is to consider
encrypting the sensitive data using a key which is compiled into your
application. Your application can then encrypt/decrpyt the data when users
acess it, but if someone queries the tables directly then they only see the
encrypted data:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22

Simonsql

Wednesday, March 21, 2012

Database Backup

I am ultimately trying to backup my database from a deployed site to my PC's disk, because tools such as MyLittleBackup, which is provided by my hosting company, is done under http and not https.

But initally I am trying to get a backup working on my local ASP.Net development server. I am getting the following error message;

"Could not locate entry in sysdatabases for database 'MyDatabase'. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally."

My stored procedure is;

ALTER PROCEDUREdbo.DBBK1

AS

BEGIN

BACKUP DATABASE MyDatabase

TO DISK='C:\Documents and Settings\BBBN\My Documents\MyDatabase.bak''

WITHFORMAT;

END

RETURN

My Stored Procedure is called by the following script:

cnn1.ConnectionString = "Data Source=.\sqlexpress;" & "Integrated Security=SSPI;" & "User Instance=True;" & "AttachDBFileName=" & My.Computer.FileSystem.CombinePath( _
Server.MapPath("/MyWebSite/App_Data"), "MyDatabase.mdf")

cmd3.Connection = cnn1
cmd3.CommandText = "DBBK1"
cmd3.CommandType = Data.CommandType.StoredProcedure

cnn1.Open()
Try
cmd3.ExecuteNonQuery()
Catch ex As Exception
Message1.Text = (ex.Message)
Finally
cnn1.Close()
End Try

I'm not sure what I need to consider to progress this. Any help would be appreciated.

Chris

Try running
USE MASTER
SELECT name, database_id FROM sys.databases

This will list all databases on your server.

|||

Hi, thanks for you post

If I add USE MASTER to my stored procedure, I get the error, a USE database statement is not allowed in a procedure, function or trigger.

I did previously use a similar SELECT statement and the database was listed in my output view. I can't get that list back now though for some reason.

Obviously my database is there because it's sourced by other routines within the site so I still unsure how to proceed

Chris

|||

After another 24 hours, I am still unable to resolve this. If anyone can point me in the right direction, that would be appreciated.

I have changed from using a stored procedure and my code is as follows in my deployed site.

cnn1.ConnectionString = "Data Source=host's server name;Initial Catalog=Mydatabase;User ID=BlahBlah;Password=BlahBlah"
cmd3.Connection = cnn1
cmd3.CommandType = Data.CommandType.Text
cmd3.CommandText = "USE MASTER;" & "BACKUP DATABASE MyDatabase TO DISK = 'C:\Program Files\Microft SQL Server\MSSQL.1\MSSQL\Backup\\MyDatabase.bak'" & "WITH FORMAT;"
cnn1.Open()
cmd3.ExecuteNonQuery()
cnn1.Close()

The error message is as follows:

Server Error in '/' Application.
------------------------

BACKUP DATABASE permission denied in database 'MyDatabase'.
BACKUP DATABASE is terminating abnormally.
Changed database context to 'master'.

Chris

|||

What permissions does User ID=BlahBlah have on the database you are trying to back up? Does that user have db_backupoperator on that database?

|||

Hi

I'm not sure, User ID and password are simply the SQL server authentication login and password. I haven't set up any specific permissions.

Chris

|||

Open your SQL Server Management Studio (or even Enterpise Manager).
Click on your database (MyDatabase) node, then Security node, then Users, then right click on the user you are using (e.g. BlahBlah) and select Properties from the list.
Now, a new windows is opened and you will find some checkboxes for Roles, check the box for db_backupoperator.

Tip:
I suggest you to have one database user for backup with db_backupoperator role and use it always from backup/restore operations.

Good luck.

|||

I've done something wrong. I checked the box for db_backupoperator for owned schemas instead of roles. Now I appear to have lost all my tables and I can't un-check the box. The run error I'm getting is;

Server Error in '/' Application.
------------------------

The EXECUTE permission was denied on the object 'aspnet_Membership_GetPasswordWithFormat', database 'MyDatabase', schema 'dbo'.

Chris

|||

I'm back to where I was before my previous post. I have checked the db_backupoperator box in roles but I still get the same error which is ;

Server Error in '/' Application.

BACKUP DATABASE permission denied in database 'MyDatabase'.
BACKUP DATABASE is terminating abnormally.
Changed database context to 'master'.

My user ID and password were set up through helm, so is the permission denied a hosting issue. My connectionstring works correctly throughout the site and I believe my Backup syntax is correct.

My Backup syntax states "Use master" yet I still get the error "Changed database context to 'master'

Any suggestions would again be appreciated.

Chris

|||

ChrisCB:

in '/' Application.
------------------------

The EXECUTE permission was denied on the object 'aspnet_Membership_GetPasswordWithFormat', database 'MyDatabase', schema 'dbo'.

Chris

Open your SQL Server Management Studio (or even Enterpise Manager).
Click on your database (MyDatabase) node, then Security node, then Users, then right click on the user you are using (e.g. BlahBlah) and select Properties from the list.
Now, a new windows is opened select "Securables" from the left side of the window, click on Add button, then "All Objects of Type", then "Stored Procedues", then OK.
Now you suppose to get a list of stored procedures in the database, click on 'aspnet_Membership_GetPasswordWithFormat' stored procedure and check the "Grant" box for it.
Now that database user can execute the 'aspnet_Membership_GetPasswordWithFormat' stored procedure and the message appeared should go.

Good luck.

|||

ChrisCB:

I'm back to where I was before my previous post. I have checked the db_backupoperator box in roles but I still get the same error which is ;

Server Error in '/' Application.

BACKUP DATABASE permission denied in database 'MyDatabase'.
BACKUP DATABASE is terminating abnormally.
Changed database context to 'master'.

My user ID and password were set up through helm, so is the permission denied a hosting issue. My connectionstring works correctly throughout the site and I believe my Backup syntax is correct.

My Backup syntax states "Use master" yet I still get the error "Changed database context to 'master'

Any suggestions would again be appreciated.

Chris

Yes, I believe it is from ythe hosting company.

Check this link:http://support.microsoft.com/kb/290787

Good luck.

|||

Hi

I've had a reply from my hosting company and the problem is they say;

"You won't be able to create your own script to backup databases in a secure shared hosting environment. This is the reason we provide MyLittleBackup."

This being so, it brings me back to my first post on this thread and my initial problem. I have a secure shared hosting environment where customer information can be collected securely over SSL. If I were to have a database failure, the only backup facility appears to be MyLittleBackup which passes the backup and restore data un-secured over the internet, which defeats the purpose of having SSL in the first place. (Its like locking the front door, but not the back door).

I would really appreciate anyone's comments as to be best way to procced from here.

Chris

|||

ChrisCB:

Hi

I've had a reply from my hosting company and the problem is they say;

"You won't be able to create your own script to backup databases in a secure shared hosting environment. This is the reason we provide MyLittleBackup."

This being so, it brings me back to my first post on this thread and my initial problem. I have a secure shared hosting environment where customer information can be collected securely over SSL. If I were to have a database failure, the only backup facility appears to be MyLittleBackup which passes the backup and restore data un-secured over the internet, which defeats the purpose of having SSL in the first place. (Its like locking the front door, but not the back door).

I would really appreciate anyone's comments as to be best way to procced from here.

Chris

I suggest you to ask the hosting company about this security isse, why they offer it in this way?
If their answer is okay for you conntinue with them, otherwise go for another hosting company.

It is really strange!

Good luck

|||

>I would really appreciate anyone's comments as to be best way to proceed from here.

Are you able to create a extra site to run a web service and run this using SSL? If so you can expose the database via a series of methods one per table. Provided your home system has a fixed external IP address, you can the lock the web service site to response only to that IP address.

|||

Sorry for the delay in replying.

CS4Ever, I think at the moment I will have to rely on backups provided by the hosting company on their servers.

Tatworth, Using a web service in the way you descibe sounds very interesting. I haven't used them before so I will look at it at some point but it seems quite a complicated task at this late stage of my sites development.

Many thanks for your help and time.

Chris

.

Thursday, March 8, 2012

database access with custom assembly

Hi,
I got a problem accessing the database from within the deployed report.
The report works perfectly well within my IDE (VS 2005 v8.0.5x) but when I
deploy it to the Report Server (SQL Server 2005, v 9.00.1399) no access to
the database can be established; the field containg the value shows '#ERROR'.
I adjusted the following things:
1) C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\rssrvpolicy.config
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="RSHelperCodeGroup"
Description="Code group for InfoFrame RS help functions">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin\RSHelpFunction.dll" />
</CodeGroup>
This entry follows the following entry:
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="$CodeGen$/*" />
</CodeGroup>
2) function within dll:
public static string getData (string id)
{
private static string connstring = "Data Source=localhost;Network
Library=DBMSSOCN;Initial Catalog=TPP;User ID=userID;Password=password;";
SqlConnection connection = new SqlConnection(connstring);
string resultString = "";
try
{
connection.Open();
string select = "SELECT Text FROM tblXY where tblXY.ID = " + id;
SqlCommand comm = new SqlCommand(select, connection);
SqlDataReader reader = comm.ExecuteReader();
bool r = reader.Read();
//...
resultString = (string)reader.GetSqlString(0);
//some checks following
...
}
finally
{
if (connection != null)
connection.Close();
}
return resultString;
}
3) Assembly
I compiled the assembly (dll) with the following attribute:
[assembly: AllowPartiallyTrustedCallers]
4) Path for the dll
I copied the dll into the following folders:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin\RSHelpFunction.dll
and
C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\PrivateAssemblies\RSHelpFunction.dll
It seems to me that this is a problem concerning the permission required for
executing the report on the server.
Any help is appreciated.
Thanks in advance.
HaraldHi,
the solution to my problem is, as I suspected, a security point.
I had to give special rights to the database access before opening the
connection to the database within the dll:
try {
SqlClientPermission perm = new
SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
perm.Assert();
connection.Open();
...
} ...
The rest of my coding and adjustments (i.e. CodeGroup,...) were ok.
Then everthing works fine even for the deployed report.
Regards
Harald
"Harald" wrote:
> Hi,
> I got a problem accessing the database from within the deployed report.
> The report works perfectly well within my IDE (VS 2005 v8.0.5x) but when I
> deploy it to the Report Server (SQL Server 2005, v 9.00.1399) no access to
> the database can be established; the field containg the value shows '#ERROR'.
> I adjusted the following things:
> 1) C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\rssrvpolicy.config
> <CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="RSHelperCodeGroup"
> Description="Code group for InfoFrame RS help functions">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\bin\RSHelpFunction.dll" />
> </CodeGroup>
> This entry follows the following entry:
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="$CodeGen$/*" />
> </CodeGroup>
> 2) function within dll:
> public static string getData (string id)
> {
> private static string connstring = "Data Source=localhost;Network
> Library=DBMSSOCN;Initial Catalog=TPP;User ID=userID;Password=password;";
> SqlConnection connection = new SqlConnection(connstring);
> string resultString = "";
> try
> {
> connection.Open();
> string select = "SELECT Text FROM tblXY where tblXY.ID = " + id;
> SqlCommand comm = new SqlCommand(select, connection);
> SqlDataReader reader = comm.ExecuteReader();
> bool r = reader.Read();
> //...
> resultString = (string)reader.GetSqlString(0);
> //some checks following
> ...
> }
> finally
> {
> if (connection != null)
> connection.Close();
> }
> return resultString;
> }
> 3) Assembly
> I compiled the assembly (dll) with the following attribute:
> [assembly: AllowPartiallyTrustedCallers]
> 4) Path for the dll
> I copied the dll into the following folders:
> C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\bin\RSHelpFunction.dll
> and
> C:\Program Files\Microsoft Visual Studio
> 8\Common7\IDE\PrivateAssemblies\RSHelpFunction.dll
> It seems to me that this is a problem concerning the permission required for
> executing the report on the server.
> Any help is appreciated.
> Thanks in advance.
> Harald