Hi

How do you give your customers access to this resource whilst maintaining security?

SQL Server can be setup for a shared environment, multiple user accounts, assigned different DB roles on databases etc..

If your not familar with SQL server security or Microsoft security in general then I'd recommend you hire an expert or do the MCP/MCSE certification (taking SQL server 2000 admin and SQL server 2000 design as your electives) . It's far too much to go into in one post or even one chapter.

Yeah - I meant more, do people grant open to net access or are there better options - customer control panel access etc?

With SQL Server I'd expect to be able to connect with SQL client tools (Enterprise Managere etc...). There isn't a PHPMyAdmin variant that can fully expose SQL server.

If you open SQL for access via the net, then it's open to all and security will be enforced by your local account security.

I'd block port 1434 (exploited by Slammer !), unless you need multiple instances of SQL. Ensure you have SP3a and make sure all passwords are secure (perhaps run a cracker).

Database & application roles will assist you with security, but at the end of the day it's down to password security.

Once you open port 1433 (or whatever you choose for SQL), then all admin and normal data read/writes all happen over the same port and can be run by anyone.

For example,

If you give me your "sa" password, and open the
firewall on port 1433, then I can do complete SQL admin on the box in the same way as if I was on the console !

If you give me a customer account and remote access, I can then guess your "sa" password and use the same connection to control your box, you can't stop me once I have the password.

Additionally if you don't keep the box secure (i.e. not install SP's), then I can gain access via known exploits and gain "sa" rights.

Also via a TSQL command, I can read / write your registry, execute "dos" commands, execute COM objects, control your domain or AD (if you don't follow MS's guidelines about not installing SQL on a DC)

You need to fully understand MS-SQL Server before letting any users onto it.

PS I'm an MCP/MCDBA and run production systems on MS-SQL 6.5/7/2000.

I give Enterprise Manager access to clients, normally they expected such access too. But I stopped giving any more mS SQL services once my server is full. Really cant afford the MS SQL license per CPU. But I seen a lot giving MS SQL as an added on free feature. How do they manage that?

MS products are expensive on a low volume basis, but they give big discounts to large volume customers.

Without offering Enterprise Manager access you'll have very few SQL Server clients - it's a must have.

Setting SQL server to listen on a non-standard port is a good start and I don't believe enyone's mentioned that yet.

I did hint at changing ports, but remember if you do, you'll have to support all your clients who don't make the change and can't connect after installing the SQL client tools.

Write yourself a How To for SQL EM, ODBC, ASP etc... and get it on your support site.

Changing the port doesn't make you secure though, you just appear on different port, you can't hide :-)

Its a good one isnt it really - its a shame there isnt a tool like phpmyadmin for ms SQL.

There is a web data administrator available, look at the microsoft SQL Server page, this may provide what you're looking for.

www.microsoft.com/sql

It won't replace SQL EM, but should do the trick for basic users.

There are lots of phpmyadmin type tools for SQL Server but the point is developers want to use Enterprise Manager and Query Analyzer - nothing else will do.