September 8, 2011 at 5:45 pm
I have a remote machine where my SQL Server server resides. I have set up SQL Server authentication for the server to accept login with User Id + Password.
I am able to login from my local machine to SS on the remote machine using the user ID and password that I've configured for SQL Server authentication. However, I've provided the login credentials to other users on the network who need to access the database for reporting purposes and unfortunately, no one else appears to be able to login to the server using the SS login credentials that I've configured.
I'm thinking that this is probably some sort of permissions issue. The users should be able to access the database that I've given them credentials for. However, I'm guessing there are some higher-level machine-level permissions that I need to configure so they have access to the actual machine. Does this appear to be a reasonable assessment? Can you please provide some guidance on where else I need to grant permissions to other network users?
I'm not sure if this makes a difference but the actual database files (mdf/ldf) are stored on the D drive while the SQL Server instance is installed on the C drive. So I'm not sure if additional permissions configuration is needed for that....
September 8, 2011 at 5:49 pm
Does the remote server have a firewall running?
Go into SQL server configuration manager on the remote server and check the protocols enabled.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 8, 2011 at 6:00 pm
Hi,
Have a look in the "Server Properties" - "Connections", if Remote Server Connections are allowed.
Regards
Karl
September 8, 2011 at 6:31 pm
Karl-Marten Paulsen (9/8/2011)
Hi,Have a look in the "Server Properties" - "Connections", if Remote Server Connections are allowed.
Regards
Karl
Well - I can connect from my local machine to the remote server so I would guess that Remote Server Connections are allowed. Otherwise, I wouldn't be able to connect to the remote server from my local machine, right?
September 8, 2011 at 6:36 pm
Perry Whittle (9/8/2011)
Does the remote server have a firewall running?Go into SQL server configuration manager on the remote server and check the protocols enabled.
I can connect to the remote server from my local machine. It's just that other users can't connect to the server from their remote machines....
September 8, 2011 at 6:42 pm
It seems like I may need to add the network user ID's of the other users somewhere on the remote machine so they can access the remote database through their SSMS. I'm just not sure where I'm supposed to configure this - I'm not a DBA.
September 8, 2011 at 8:31 pm
I guess I should ask the following general question to those with more dba experience than myself. If I set up a database with SQL Server authentication then should any user on the network be able to login with the SQL Server credentials? Or do I also need to grant additional permissions to the other network user ID's to be able to access the server? Other network user ID's don't have access to the server by default. For example, I recently created a shared directory and granted it to one of the users. I would think that SQL Server authentication credentials would give anyone with those credentials access to the server. That's just my guess and I don't know where additional sharing would be required.
September 9, 2011 at 1:45 am
sqlguy-736318 (9/8/2011)
It seems like I may need to add the network user ID's of the other users somewhere on the remote machine so they can access the remote database through their SSMS. I'm just not sure where I'm supposed to configure this - I'm not a DBA.
no, you have configured SQL Server authentication and created a SQL Server account have you not?
please connect up yourself and do the following then post the results
Open sql server management studio and connect to the sql server instance. In the left pane right click the instance name and select properties. On the pop up dialog on the left side of the window select the security option. On the security page what is the server authentication mode set to?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 9, 2011 at 1:49 am
sqlguy-736318 (9/8/2011)
I guess I should ask the following general question to those with more dba experience than myself. If I set up a database with SQL Server authentication then should any user on the network be able to login with the SQL Server credentials?
once you have enabled remote connections.
sqlguy-736318 (9/8/2011)
For example, I recently created a shared directory and granted it to one of the users. I would think that SQL Server authentication credentials would give anyone with those credentials access to the server. That's just my guess and I don't know where additional sharing would be required.
No, windows file system and sql server access are completely different. Sql server accounts have no access to windows at all. A windows account granted access to the instance will not necessarily have access to filesystems it could but thats a separate admin path.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 9, 2011 at 3:13 am
once u set up sql server in remote machine u will be able to access it but following condition has to be met.
1) go to configuration manager and enable tcp/ip.
2) check the sql server services is running of that instance.
3) check the sql server browser is in running mode.
for help follow this link.
http://www.smallworkarounds.net/2009/09/unable-to-connect-to-sql-server-2008.html
September 9, 2011 at 8:14 am
Perry Whittle (9/9/2011)
sqlguy-736318 (9/8/2011)
It seems like I may need to add the network user ID's of the other users somewhere on the remote machine so they can access the remote database through their SSMS. I'm just not sure where I'm supposed to configure this - I'm not a DBA.no, you have configured SQL Server authentication and created a SQL Server account have you not?
please connect up yourself and do the following then post the results
Open sql server management studio and connect to the sql server instance. In the left pane right click the instance name and select properties. On the pop up dialog on the left side of the window select the security option. On the security page what is the server authentication mode set to?
"SQL Server and Windows Authentication mode"
September 9, 2011 at 8:19 am
Perry Whittle (9/8/2011)
Does the remote server have a firewall running?Go into SQL server configuration manager on the remote server and check the protocols enabled.
In each configuration node of the configuration manager "TCP/IP" is enabled. Is that essentially what allows remote machines to be able to access the server? Is this considered the firewall for SQL Server or just a way to create an exception for the firewall?
September 9, 2011 at 8:23 am
Karl-Marten Paulsen (9/8/2011)
Hi,Have a look in the "Server Properties" - "Connections", if Remote Server Connections are allowed.
Regards
Karl
Yes - "Allow remote connections to this server" is enabled. But that's good advice on something to check for. Thanks.
September 9, 2011 at 8:44 am
ghanshyam.kundu (9/9/2011)
once u set up sql server in remote machine u will be able to access it but following condition has to be met.1) go to configuration manager and enable tcp/ip.
2) check the sql server services is running of that instance.
3) check the sql server browser is in running mode.
for help follow this link.
http://www.smallworkarounds.net/2009/09/unable-to-connect-to-sql-server-2008.html
ghanshyam - your #1 and #2 conditions are met on the server. However, #3 is not - "SQL Server Browser" is disabled.
The service description for "SQL Server Browser" is "Provides SQL Server Connection Information to client computers."
Can someone explain "SQL Server Browser" to me in a nutshell? I found this url http://msdn.microsoft.com/en-us/library/ms165724(v=sql.90).aspx but it's too verbose for me.
September 9, 2011 at 9:52 am
i think that one, where u r stucking, just start it once and try it should work, i also have faced the same problem. i have done the same it worked.
If SQL Server Browser service is stopped and IP along with port number is not provided then connection will be refused
go through the link for know more about sql server browser details
http://www.mssqltips.com/sqlservertip/1946/overview-of-the-sql-server-browser-service/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply