Dotnetpanel Forums

I am very new, how do I setup MSSQL Hosting (using MSSQL 2000 now).

Some questions that I am facing:

1. How do I block the user to see other login?
2. How do I block other user to see other database?
3. How do I block other user to restore / backup on site?
4. Is SQL 2005 better in term of easier to manage and secure by default?
5. How do I remove the guest user, I can't delete it, it said it is controlled by Group membership, and when I delete it, it said there is not such user.
   

I have one main question, actually when hosting other people databases, they usually pass the database for restore, do I need to do anything beside setting the correct path to restore?

What permission should I give after I restore, public, db_owner, data reader, data writer? That's it. Correct?

Thank you.

 

You are lucky. Microsoft published a document on MSSQL 2005 for web hosting last week. It includes a script to create the db, secure it, ... I was very happy when a forum poster on another forum adviced me this. I hope you will be too. :-)

 http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/SQL2005DGWHE.mspx

Anyone know if theres a script that stops Database users seeing other Databases.

I know theres one for SQL 2000 is there one for SQL 2005/Express

If you connect with a web frontend to your DB then users can not see other users databases.

When you connect directly over the internet (security risk is higher, therefore not suggested) I don't know for sure, but I think they will not see the other databases when you follow the script that I posted ealier today. Have a look in the article the script is explained in detail. I think to remember that the deactive this in a step. Look it up and do the same.

Hope this helps.

wimvm:

If you connect with a web frontend to your DB then users can not see other users databases.

When you connect directly over the internet (security risk is higher, therefore not suggested) I don't know for sure, but I think they will not see the other databases when you follow the script that I posted ealier today. Have a look in the article the script is explained in detail. I think to remember that the deactive this in a step. Look it up and do the same.

Hope this helps.

 Thanks

wimvm:

You are lucky. Microsoft published a document on MSSQL 2005 for web hosting last week. It includes a script to create the db, secure it, ... I was very happy when a forum poster on another forum adviced me this. I hope you will be too. :-)

 http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/SQL2005DGWHE.mspx

Nice document, thanks for the link!

Thanks for the links.

By the way, one of main point is to disable the backup and restore on the server over the Internet, without total blocking the SQL Management Studio access from the client.

I am not sure how to do it.

And, is Windows Accounts instead of SQL is more secure right? How about set in web.config file when deploy customer web application,

I hope someone will answer me, thanks in advance.
 

If you have a look an read the link on the MS website you will find a section on SQL directly connected on the internet. The statement is very clear, they do not advice to use SQL directly connected on the internet. I also don't see any point in why backups/restores would be that different from normal manipulation of databases. I don't think you can block the backup/restore function when you allow them to connect via the management interface.

To what I understand from it, it is not that Windows is more secure, but it is more easy to manage one security system then two. In the example as they suggest (see link) they also use SQL authentication (correct me if I am wrong). I don't think that SQL security is less secure.

If you work in a front-end web and back-end SQL senario you can just use a normal connection from the web to the sql as a user. The authentication is never exposed to the internet then, because it goes via the private LAN. You even can encrypt it (but that is up to the user). Depending of that the user need write rights in the database you can even restrict only read for the user that connects to the database. Refer to www.asp.net for more info about this subject.

Hi, thanks. Do I actually need to install Analysis Services? or I should just install the Database Engine, that all. Thanks.

Feodor will you be adding any of this into the 2.1 release?

would be nice if DNP could take care of it all.

Also, myLittleAdmin is pretty sweet... have you though about buying their "BIG" license and packaging it with DNP?  I would pay for that as it would probably be a little less than buying it one at a time.

Or if DNP became a reseller and added it to the server modules or something, and by buying it in larger quantities you could pass those savings on.

rockinthesixstring:

Feodor will you be adding any of this into the 2.1 release?

would be nice if DNP could take care of it all.

We have investigated this problem and the only ways to resolve this for SQL Server 2005:

a) limit the number of databases that can be assigned to the certain user account to one database only and make it as db_owner. Other words the same user can be owner of one database only.

b) wait for the improvements in SQL Server 2005. I've seen a blog post of some guy from SQL team saying about this problem.

rockinthesixstring:

Also, myLittleAdmin is pretty sweet... have you though about buying their "BIG" license and packaging it with DNP?  I would pay for that as it would probably be a little less than buying it one at a time.

Or if DNP became a reseller and added it to the server modules or something, and by buying it in larger quantities you could pass those savings on.

Yep, myLittleAdmin is a great tool. We are authorized myLittleTools partners and all DNP customers can purchase myLittleAdmin with 25% discount

Can you explain what the "this problem" exactly is?

The problem is when somebody connects to remote SQL Server 2005 via SSMS he should see only his own databases.

Of course everything is OK when you use myLittleAdmin to manage databases.

Feodor Fitsner:

Of course everything is OK when you use myLittleAdmin to manage databases

So basically there is no problem, users should use myLittleAdmin :-)

Definitely, this is a good option and MLA works much quicker than SSMS connected to the remote SQL server. However, SSMS Express is free.

As for me I used to do all database modifications locally and then upload/restore it to/on production.