none
Login failed for user 'USERNAME' ERROR RRS feed

  • Question

  • I am trying to connect a system database to R.This is the code I use and the error that occurs

    dbConnect(odbc(),
                     Driver = "SQL Server",
                     Server = "KATHY",
                     Database = "master",host = "localhost"
                     ,Port = 1433,uid="USERNAME", pwd="PASSWORD")

    Error: nanodbc/nanodbc.cpp:950: 28000: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'USERNAME'. 

    I have tried switching from Windows Authentification to SQL Server and Windows Authentication and the restarted the system.However nothing seems to be working.I'm wodering If this occurs because I'm using a system database or because I still use the Windows Authentification option when trying to connect to my database and there's no username or password needed.

    Tuesday, January 1, 2019 5:43 PM

All replies

  • Look in the SQL Server error log. There should be information about why you cannot log in. You find the error log in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\LOG\ERRORLOG. Note that the exact path depends on your installation.

    Also, you should use a better driver. The one called SQL Server is almost twenty years old, and supports only features up to SQL 2000. Preferrably, you should use the ODBC Driver for 17 for SQL Server driver which you may have to download separately.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, January 1, 2019 9:30 PM
  • Error: nanodbc/nanodbc.cpp:950: 28000: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'USERNAME'. 

    Hi kat_tzn,

    Based on the error message above, have you tried to connect to SQL Server through the tool SSMS? Does it still get that error message?

    Besides, you could follow this article to troubleshoot that issue.

    "Login failed for user" error message when you log on to SQL Server

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 2, 2019 2:13 AM
    Moderator
  • If you can get someone to login to the SQL Server from SQL Server Management Studio (SSMS) using USERNAME and PASSWORD and see if they get any errors and can they access the master database tables. Then have USERNAME (or some other login if USERNAME is not able to login) and run this bit of tsql in a query window. Change the PASSWORD to your actual password for USERNAME. It will return the entered password in the PasswordMatch column for any tsql logins that use that password. It will also tell you if the login is disabled, expired, or locked which is a bit tricky to discover and can take a lot of time to find out. So is USERNAME a SQL Login, then is PASSWORD the password for USERNAME, is USERNAME not disabled/expired/lock, and does USERNAME have permissions for the master database. 

    Part of the reason for the tsql code is to find if login is disable/expired/locked but also, it will not count as an invalid login attempt. The login can be setup to disable after a certain number of invalid login attempts.

    Declare @SQLLoginPassword varchar(128) = 'PASSWORD'  -- replace this with the password 
    
    SELECT  p.name as SQLLogin,CASE WHEN l.name IS NOT NULL THEN @SQLLoginPassword ELSE '' END as PasswordMatch,ISNULL(is_disabled, 0) as is_disabled
          ,ISNULL(LoginProperty(p.name, 'IsExpired'), 0) as is_expired,ISNULL(LoginProperty(p.name, 'IsLocked'), 0) as is_locked
           FROM sys.server_principals p left join 
                  (SELECT name FROM sys.sql_logins WHERE PWDCOMPARE(@SQLLoginPassword, password_hash) = 1) l
                      on p.name = l.name
            WHERE p.type = 'S' and p.name not like '##%' ORDER BY p.name


    Wednesday, January 2, 2019 1:34 PM