asked
Enable Remote Access
I am working on procedures for creating a new server. One of the things I constantly have to do each time SQL is installed is enable remote connections. Is there a way to do this with T-SQL rather than using the GUI? I would like to have a script to do as much of the administrative stuff as possible. SQL 2005/2008 are the environments I am concerned with.
Yes this can be done with T-SQL. EXEC sys.sp_configure N'remote access', N'1' GO RECONFIGURE WITH OVERRIDE GO
I have to challenge you a little on this one. IASE database checklist requires this setting to be disabled if linked servers are not in use. I have a production cluster where this is disabled, cause we do not use linked servers. Their web servers are still allowed to connect to SQL Server. Going by what BOL says "remote access" setting will be depreciated (
http://msdn.microsoft.com/en-us/library/ms187660(v=SQL.100).aspx). Then also reading through this article (
http://msdn.microsoft.com/en-us/library/ms190944(v=SQL.100).aspx) for SQL Server 2008, all you need to do is enable TCP/IP and Named Pipes protocol to allow remote connections. With SQL Server 2005 you have to use Surface Area Configuration to enable remote connection through TCP/IP and Named Pipes, otherwise it will only accept local connections.
Isn't that setting regarding allow linked server access? Is he referring to enable remote access on TCIP/IP and named pipes, as you would do through Surface Area Configuration Manager on SQL 2005? That is disabled by default on installation. Because BOL does show this setting is going away in future releases.
The query above is not related to linked server, but rather allowing connections other than the local server through shared memory to connect. If TCP/IP and Named Pipes are enabled then those connections could be made if the "remote access" is enabled. I have updated my answer with a screen shot of the setting in the GUI using SSMS 2008. I right clicked on the server, chose properties, and click connections. OK, so it won't let me attach the image.
You do bring up a valid point @meltondba. I am not sure what @mikelanders justification or reasoning is for needing to turn that on unless his environment. But I do think the response I gave him is what he was asking for. How to enable remote access from within TSQL rather than the GUI. I recall getting messages before when I first installed SQL 2005 trying to connect from SSMS from another workstation that gave me a message about having to enable remote connections. I am going to fire up a VM and play with this.
Thanks guys. @meltondba you bring up some valid points with the links. @trad, yes this is what I needed.
@mikelanders so you are referring to enabling access for linked servers, not just connection to the SQL instance itself?
