VBA - Access to the remote server is denied because no login mapping exists

• khurmi.navpreet

  Old Hand

  Points: 376

  More actions

  August 14, 2013 at 9:52 pm

  #74183

  Hello,

  I had a VBA code which has been running fine for the last one year and then it suddenly stopped working.

  Basically in this code I am just executing a Stored Procedure on a SQL Server Database.

  Also I checked and the readonly user does exist.

  connStr = "Driver={SQL Server};Server=SQL20;Database=ABC;user id=readonly;password="

  Dim Cmd1 As ADODB.Command

  'Setup the connection to the database

  Dim connection As ADODB.connection

  Set connection = New ADODB.connection

  connection.ConnectionString = connStr

  'connection.ConnectionTimeout = 500

  connection.Open

  'Open recordset

  Set Cmd1 = New ADODB.Command

  Cmd1.ActiveConnection = connection

  Cmd1.CommandText = "SPCreate"

  Cmd1.CommandType = adCmdStoredProc

  Cmd1.Parameters("@pileNum").Value = pileNum

  Cmd1.Parameters("@startDate").Value = CDate(startDt)

  Cmd1.Parameters("@endDate").Value = CDate(endDate)

  Dim myRecordset As ADODB.Recordset

  Cmd1.CommandTimeout = 500

  Set myRecordset = Cmd1.Execute() - THIS IS WHERE IT GIVES THIS ERROR (Access to the remote server is denied because no login-mapping exists)

• HanShi

  SSC-Dedicated

  Points: 33514

  More actions

  August 14, 2013 at 11:44 pm

  #1641648

  Please make it clear if it involves a MS Access database (because you posted this in the MS Access forum) or a SQL Server database (like you mention in the post).

  If it involves SQL:

  - Check for any errors in the SQL Error log

  - Check if the login you use still exists on the instance

  - Check if the login has the correct permissions on the database

  - Check if the login is connected to the user in the database: use {db_name};exec sp_change_users_login 'Report'

  If it involves MS Access:

  - Can you manually start MS Access, open the database and execute the command (using the user credentials you use in the code)

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• khurmi.navpreet

  Old Hand

  Points: 376

  More actions

  August 15, 2013 at 2:36 am

  #1641684

  - Check for any errors in the SQL Error log - NO ERROR

  - Check if the login you use still exists on the instance - DOES EXIST

  - Check if the login has the correct permissions on the database - YES

  - Check if the login is connected to the user in the database: use {db_name};exec sp_change_users_login 'Report' - Executed this command but it gave me only 2 usernames with UserID's.

• HanShi

  SSC-Dedicated

  Points: 33514

  More actions

  August 15, 2013 at 2:43 am

  #1641692

  khurmi.navpreet (8/15/2013)- Check if the login is connected to the user in the database: use {db_name};exec sp_change_users_login 'Report' - Executed this command but it gave me only 2 usernames with UserID's.

  The 2 usernames with the UserID's indicate that a connection between the user and the login is lost. The connection is lost due to a mismatch between the UserID's of the Login and the corresponding User. Most probably a database restore is executed or the login is deleted and re-created.

  You can use the following command for each listed username to re-connect them again:

  use {db_name};exec sp_change_users_login 'Auto_fix', '{username}'

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **