I am trying to connect My R studio to SQL Database on the Remote Desktop

Question

The SQL Server on the remote desktop is configured to have neither login nor password, it is configured to use Windows Authentication.

Code

I am using the following code to connect:

library(DBI)
library(RODBC)
dbhandle <- odbcDriverConnect('driver=SQl Server;server=FAC-270938-ENG2;Database=HRMS')

Error Generated

Warning messages:

1:

    In odbcDriverConnect("driver=SQl Server;server=FAC-270938-ENG2;Database=HRMS") :
      [RODBC] ERROR: state 28000, code 18456, 
message [Microsoft][ODBC SQL Server Driver][SQL Server]

Login failed for user 'HMINDIA\274074'

2:

In odbcDriverConnect("driver=SQl Server;server=FAC-270938-ENG2;Database=HRMS")
   ODBC connection failed

Answer 1

Basically, when you failed to connect to your SQL Server, the issue could be:

1. Network issue. SQL Server configuration issue.
2. Firewall issue.
3. Client driver issue.
4. Application configuration issue.
5. Authentication and logon issue.

Check this link for more details.

In your error message it is just a login failure:

Login failed for user 'HMINDIA\274074'

you need to create a login. there are windows logins and sql logins:

for example (windows login - attached to the active directory):

USE [master]
GO

CREATE LOGIN [my_domain\my_login] 
FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

--grant the access and permissions accordingly
--in this case it is a sysadmin
--ALTER SERVER ROLE [sysadmin] ADD MEMBER [my_domain\my_login]
GO

example of creating a sql login:

USE [master]
GO


CREATE LOGIN [my_ETL_login] WITH PASSWORD=N'Hare_Krishna108', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- grant access to this login
-- this login will be able to access and read database CAMemeMail:

    USE [CAMemeMail]
    GO
    CREATE USER [my_ETL_login] FOR LOGIN [my_ETL_login]
    GO
    USE [CAMemeMail]
    GO
    ALTER USER [my_ETL_login] WITH DEFAULT_SCHEMA=[dbo]
    GO
    USE [CAMemeMail]
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [my_ETL_login]
    GO

And don't forget the firewall