I have been having a problem, each of our departments is on a separate sub net e. 165.10.xx.xx where my server exists on 165.10.10.10 other worksatations upon the same sub net can access the database, where as those on a separate sub-net e.g. 165.10.12.25 cannot.
The particular data base is a an sqlexpress database, which is accessed through an access-front end utilizing ODBC for the connectivity. The user does have access rights via windows security to both the database directory, as well as the front access specific directory. When trying to set up the ODBC driver I cannot browse to the server, I can however type it in e.g. host\sqlexpress i do get through the authentication screen utilizing windows authentication. When I try to change the database from master to the application database I get an error stating database does not exist.
I have confirmed that the sql server is listening (which seemed obvious that same sub-net could get in) I have also assured the problem sub-net workstation is not running any fire wqall blocks or port filters.
--Today I logged on to the work station as myself (system administrator) and I can set up the ODBC driver under my rights, once i typed in the server name, I could see the database I needed. This gave me the idea of adding the user in the problem sub-net to the server administrators group -- which does allow the user access.
I had thought that the secure by design paradigm shift allowed access with minimal rights.
My question is this :
How do I configure the server so that the user is not required to be an administrator on it, so that access to the data can be granted, while keeping a modicum of security on the server?
Any help would be most welcome -- Thanks ;-)
Are you successfully able to connect to the server on subnet 165.10.10.10 from different subnets after making the connecting users admins on the server machine?
It seems very odd that your network connectivity problem would be solved by adding the member to the administrators group. This is not expected and is not required. Are you using IP security in your environment? What is the precise error message that you're receiving when your connection attemp fails?
To get started on how to provision login access for non-administrator users, here's a good page: http://msdn2.microsoft.com/en-us/library/ms189751.aspx. If you look around this part of the documentation you'll find more details on the minimum set of permissions needed for users to successfully login.
Hope this helps,
Vaughn
|||
Once I add the user to the Administrators group on the host machine she can connect to the database and function.
What strikes me as odd though, is that i still have to actually type in the host/sqlexpress refrence as it will not show up in the
Which server do you want to connect to?
Server -- combo box
The authentication step works
then I can see the compulsionasset database in the
Change default database to checkbox -- drop down combo
When the user is not an administrator
I still have to type the server name, authentication still works, but the database table does not show up for selection, and typing it in I get an error that it doesn't exist so that the ODBC administrator wizard will not complete.
There are no other errors.
Adding her to the administrators group did get her in though.
|||
Oh...
I have given her a login for he domain account within SQLExpress on the appropriate table...
|||
Once I add them as administrator they can connect. It does seem crippled though, usually page ione of the ODBC setup wizard lets you choose from a drop down combo box - the server, I must type the full name in as it doesn't see it. then Page 3 usually lets you change the default database -- as an administrator they can see the database -- if not they can only see the default databases master, tempdb and model. Both means appear to connect -- without error messages on page 2 of the wizard.
|||
I'm not sure if the drop down combo box and the login problem have the same root cause but here's what I'd suggest for fixing the drop down:
1) Ensure the SQL Server instance has TCP/IP enabled (it almost certainly does since remote admin connections work)
2) Ensure the SQL Browser service is running on the server machine
3) Make sure there are no firewalls (e.g. windows firewall) blocking connectivity to the sql server instance or sqlbrowser. For SQL Browser you can either add the executable or open UDP port 1433. For the name SQLExpress instance you'll need to add the process since the TCP port is dynamic unless you've manually configured it otherwise.
4) Double check that in SQL Server Configuration Manager there's not a "HideInstance" flag set to true for the SQLExpress instance
Checking all of these *should* make the instance show up in the drop down box.
|||
All of those have been checked. I can see the server from machines within the same subnet, I cannot from machines on a different subnet. Though those users can browse the host via windows networking., there are other instances that do not show up within the drop down as well, though, again they do when on the same subnet.
All firewalls are external to the network none between subnets.
Again, thanks for any and all help.
