Connect to a named SQL Server 2016 instance from R
<p>I'm trying to connect to a SQL Server 2016 database in RStudio. I'm using RStudio on my laptop. I could remote in to the server and install RStudio there if it were absolutely necessary, but working locally has massive advantages so I would really prefer that if it were possible. Connection with the server goes through a VPN (FortiClient) that I have running on my laptop.</p>

<p>On this server, there are two SQL Server instances. One is a SQL Server 2012 edition, which is the default instance and hence not named - it used to be the only instance on this server. The other one is the 2016 edition. This instance was set up more recently in order to use the R integration capabilities new to SQL Server 2016. Because the server already had a default instance, this instance had to be named and it hence is called <code>DEVR</code>.</p>

<p>When I access the instances in SSMS and click 'Properties', the name of the default instance is <code>DWH-ACC</code> and the 2016 instance is called <code>DWH-ACC\DEVR</code>.</p>

<p>This is the code I'm running in RStudio to test my connection:</p>

<pre><code>server &lt;- "[IP-ADDRESS]\\DWH-ACC\\DEVR"
databaseName &lt;- "Database"
user &lt;- "user"
pwd &lt;- "password"
sqlShareDir &lt;- "C:\\Dir"
sqlWait &lt;- TRUE
sqlConsoleOutput &lt;- FALSE
sampleDataQuery &lt;- "SELECT TOP 10 FROM [dbo].[Table]"

cc &lt;- RxInSqlServer(server = server, databaseName = databaseName, user = user, password = pwd, shareDir = sqlShareDir, wait = sqlWait, consoleOutput = sqlConsoleOutput)
rxSetComputeContext(cc)

inDataSource &lt;- RxSqlServerData(sqlQuery = sampleDataQuery, server=server, databaseName=databaseName, user=user, password=pwd, stringsAsFactors=TRUE, rowsPerRead=500)

rxGetVarInfo(data = inDataSource)
</code></pre>

<p>I've tried several options for the server specification, among which <code>[IP-ADDRESS]\\DEVR</code> and <code>[IP-ADDRESS]/DEVR</code>, which both do not work either. This is the error I get when I run the code:</p>

<pre><code>[Microsoft][ODBC SQL Server Driver][DBNETLIB]The SQL-Server does not exist or permission has been denied.

Could not open data source.

ODBC Error in SQLDisconnect
</code></pre>

<p>(Message translated from Dutch, by the way - this may not be the exact error text in the English version of the software)</p>

<p>When I try simply the IP address as my server connection string, I get a different error that seems to indicate it is able to find the instance (the 2012 one, i.e. the wrong one) but not able to process the query.</p>

<pre><code>[Microsoft][ODBC-stuurprogrammabeheer] Fout in functievolgorde
</code></pre>

<p>I'm not sure how to translate this one, but it seems to be related to ODBC-drivers and says "error in function order". Anyway, this error is unrelated and I don't need it solved or explained, it simply goes to show that R does seem to be able to connect to the default instance but not to the newer, named one.</p>