Providing read-only access to a SQL Server database over the internet 1

[cravincreeks]

Hello,

I’m working with a remote team in California that needs read-only access to a single database (our “Geology” database) in our SQL Server here in Virginia. On the server side (Virginia), we are running Windows XP Pro SP1 and SQL Server 2000. On the client-side (California), they are running a linux (or possibly unix) box and they are attempting to connect to our database via JDBC. This description of our problem is quite long-winded, but I’m wanting to provide as much information as possible to maximize the chance of you diagnosing our problem. Thanks for reading….here goes.

Our SQL Server 2000 was recently installed and we upsized the database to which we are trying to provide access from an Access 2002 database. So currently, our SQL Server contains the following databases: our upsized “Geology” database, master, msdb, Northwind, pubs and tempdb. With the exception of correcting a few of the upsize errors in the “Geology” database (data type conversion errors, broken table links) and changing/adding user accounts, I have made no other modification to SQL Server – everything else is “as-installed”.

I added a new login using EM, titled GeologyReadOnly and assigned it no Server roles, but did assign it to have read-only access (db_datareader) to our “Geology” database, and only that database! This user account <<IS>> a member of the “public” database role and I have made no modification to the permission that the public users have by default. I have tested this GeologyReadOnly login in the associated Access Data Project that connects to our Geology SQL Server database. When logged in under this login account, I can view data in the Access tables, but I cannot modify the data. This strongly suggests that this login account is correctly configured.

During the SQL Server installation, the installation program listed the following
Named pipe: \\.\pipe\sql\query
tcp/Ip sockets: port# 1433.
I have not modified these settings, but I did go into the XP firewall (SP1) and opened port 1433. More specifically, specified the IP address of our server in the “Name or IP address” text/input box. I also specified the External Port as 1433 (TCP) and Internal Port as 1433 as well.


This paragraph describes how I have been attempting to “register” this database with the client (California) that needs read access to our database. Our counter parts in California have set up a web-browser registration form where I enter information about how to connect to our database. Please note that this registration process has been successful in allowing this client (California) to access remote Oracle and MySQL databases in other locations. Once this California client accesses these databases, it then displays the database schema to all users in our group – through this web page, I can view these Oracle and MySQL schemas. Please also note that those working on the client side (California) are a part of a university super-computer center. They know what they’re doing, but they have limited experience with SQL Server – as the typically run Unix or Linux systems. For the host name, I provided our ip address (eg. 128.xxx.xxx.xx) and port number 1433. For the database name, I’m specifying the name of the database we wish to provide read access to: “Geology”. For the username, I’ve supplied GeologyReadOnly (see above) and the password.

When I click submit, I get very strange and somewhat alarming results. Again, once I supply the California client computer with this connection information, it displays our schema (not the actual data) back through the web-browser. None of the tables and views in the Geology database, the database to which I am intending to provide read access, are shown. But some tables and views in the <<<master>>> database are shown!!!!! For example, the “COLUMN_PRIVILEGES” view is shown – again, only the column names, their data type, and column size are shown – the actual data are in these views and tables are not shown. Additionally, the schema for the “spt_datatype_info”, “spt_fallback_db” and “spt_monitor” and other tables are shown (these are found in the master database as well). So yes, this is a bit alarming – that remote users have access to our master database when they have not been granted permission to read it. Luckily, this SQL Server is its testing phase and does not contain any “sensitive” data or information – if the server is hacked into or crashed, it won’t be a big loss. A screen shot of the schema that is displayed to me from the California client after attempting to “register” this database is shown at

http://filebox.vt.edu/users/amzendel/public/sqlserverproblems/Untitled-1.htm

So why is the client able to read the master database and not the “Geology” database? Could it be that the jdbc driver that the client is using defines the commonly used view/tables in the master? In other words, is the client truly connecting to our database, or is it displaying a schema that the jdbc “expects” when connecting to SQL Server database.

I imagine that I may need to configure IIS to allow this connection, but I’m an IIS novice and would not know where to begin doing this. Also, we are running Apache and Tomcat on the server as well – which serves our ArcIMS map service (

http://www.esri.com/software/arcgis/arcims/index.html).

This ArcIMS website is accessed by clients through our port 80. Is there potential for conflicts between Apache/Tomcat and IIS/SQLServer? Could this be causing this flawed connection with our SQL Server databases?

Any help/ideas/suggestions/pointers/solutions that you can provide would be tremendously appreciated.

Feel free to email me at amzendel at vt dot edu.

 

[nigelrivett]

I would also add the login to the db_denydatawriter role to make sure they don't inherit write access from somewhere else by mistake.

First of all use the profiler to see what the remote connection is doing and how it is connecting.

If the remote connection is gaining access to master then it must doing it via the public role. Check the login from profiler.
Profiler will also show how they are getting the schema.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mrdenny]

Change the default database for the read only account to the Geology database.

When jdbc connects it connects to the default database unless you tweak the connection string.

All users have basic access to the master database, other wise they wouldn't be able to view the list of databases, or verify there username and password. All of which is stored in the master database.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[cravincreeks]

Thanks. Setting the correct default database for the uer account did the trick.

Thanks

AZ