SQL works in R studio but not in R-Serivces

Question

This code works perfectly in R-Studio but there is no way to make it work in MS Management studio. It keeps on saying that:

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'myserver\LOCAL01'.

That is not my user give it is a trusted connection. Can someone help me understand it?

ALTER PROCEDURE [dbo].[TESTIM] AS
BEGIN
    SET LANGUAGE ENGLISH
    CHECKPOINT 
    DBCC DROPCLEANBUFFERS
    EXEC sp_execute_external_script
        @language = N'R'
        , @script = N'
        con <- "Server=myserver\\LOCAL;Database=mydb;Trusted_Connection=true";
        sql <- RxInSqlServer(connectionString = con, shareDir = "c:\\TMP");
        local <- RxLocalSeq(sql);
        rxSetComputeContext(local)
        ff <- RxSqlServerData(sqlQuery = "select top 1 * from mytable", connectionString = con);
        t = rxImport(ff);
        OutputDataSet <- data.frame(SUCCESS = TRUE);
        '
    WITH RESULT SETS (([SUCCESS] BIT))
END

Answer 1

So, when you execute sp_execute_external_script it executes under one of 20 Windows user accounts (worker accounts) that has been created during installation of SQL Server R Services. These accounts are created for the purpose of running tasks under a security token belonging to the SQL Server Trusted Launchpad service.

This works very well, but if you need to create a SQL connection inside your R script (as in your case) and you use trusted connection (Windows Authentication), you are executing under the user account mentioned above ('myserver\LOCAL01' in your case), and that account need to be given permission to log in to the SQL Server instance on your behalf.

To do this:

1. In SQL Server Management Studio, in Object Explorer, expand Security, right-click Logins, and select New Login.
2. In the Login - New dialog box, click Search.
3. Click Object Types and select Groups. Deselect everything else.
4. In Enter the object name to select, type SQLRUserGroup and click Check Names.
5. The name of the local group associated with the instance's Launchpad service should resolve to something like instancename\SQLRUserGroup. Click OK.
6. By default, the login is assigned to the public role and has permission to connect to the database engine.
7. Click OK.

That should do it (the above steps are copied from here.

If you want to read more about the user accounts you can have a look at my blog-post "Microsoft SQL Server R Services - Internals III".

Hope this helps!

Niels