SQL Mobile Query

SQL Mobile Query

• GOOOGO

  thanks Darren for looking into this.

  While trying to add IUSR_<m/c name> to my publication access list, i encounter the same error. the detailed log is as follows:

  ===================================

  SQL Server could not retrieve the list of logins available to be added to the publication access list.

  ------------------------------
  For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantGetPublisherLogins&LinkId=20476

  ===================================

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  ------------------------------
  Program Location:

     at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
     at Microsoft.SqlServer.Replication.Publication.EnumPublicationAccesses(Boolean returnGranted)
     at Microsoft.SqlServer.Management.UI.PublicationAccessListPage.btnAdd_Click(Object sender, EventArgs e)

  ===================================

  Could not obtain information about Windows NT group/user '<snip>\<snip>', error code 0x54b.
  The statement has been terminated. (.Net SqlClient Data Provider)

  ------------------------------
  For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15404&LinkId=20476

  ------------------------------
  Server Name:<snip>

  Error Number: 15404
  Severity: 16
  State: 19
  Procedure: xp_logininfo
  Line Number: 60

  
  ------------------------------
  Program Location:

     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
     at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
     at System.Data.SqlClient.SqlDataReader.get_MetaData()
     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
     at System.Data.SqlClient.SqlCommand.ExecuteReader()
     at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)

  
  

• hivani

  I'm not familiar with that lab, but I suspect your issue is that you are using anonymous authentication to IIS to achieve the replication and you have not granted the IIS default account access to BOTH your publication AND to the database that is being published.  The account you want to grant access to is IUSR_{your machine name}

  After you grant this account permission to the publication and to the published database, you need to regenerate the snapshot.  Your replication should then work once the snapshot agent succeeds in generating a valid snapshot.

  -Darren Shaffer

  
  
  

• jobin thomas

  ahh, that's good news.  yes, the SQL Agent must run under credentials that allow it access to the Publisher and the specific publication.  I always run the SQL Agent with credentials that are administrator on the server to avoid any trouble creating the snapshot.

  -Darren

   

  
  
  

• joklahome

  Yes you are right. Snapshot Agent by default assumes the credentials of SQL Agent. Either of the ways are correct.

  Thanks,

  Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

  
  
  

• Ando182

  I actually ran across the same problem when executing the same HOL (Developing a SQL Mobile Application with Visual Studio 2005 and SQL Server 2005). I solved the problem by changing the owner of the "Snapshot Agent" job to sa instead of my Windows authenticated account. After changing the owner, the initial snapshot was created.

  (To change the owner of the "Snapshot Agent" job: go to Replication Monitor/My Publishers/[your machine name]/[publication], in theTab "Warnings and Agents" there is a list of Agents and jobs related to this publication. When right-clicking on the "Snapshot Agent" job that is in error, it is possible to view the properties of this job. There you can change the owner of the Snapshot Agent job)

  
  

• JH_

  thanks for being with me on this...

  i am using SQL Server 2005 Management Studio. i have added IUSR_<m/c name> to my published DB and assigned it roles of db_datareader and db_datawriter. I can see this user in the list of User for the DB.

  When i am trying to configure the replication publication, and reach the Publication Access List property and click Add, the story breaks. i get the error as mentioned in this step as mentioned earlier. i reproduce the same here:

  
  SQL Server could not retrieve the list of logins available to be added to the publication access list.

  ------------------------------
  For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantGetPublisherLogins&LinkId=20476

  ===================================

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  ------------------------------
  Program Location:

     at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
     at Microsoft.SqlServer.Replication.Publication.EnumPublicationAccesses(Boolean returnGranted)
     at Microsoft.SqlServer.Management.UI.PublicationAccessListPage.btnAdd_Click(Object sender, EventArgs e)

  ===================================

  Could not obtain information about Windows NT group/user 'xxxxxxx\xxxxx', error code 0x54b.
  The statement has been terminated. (.Net SqlClient Data Provider)

  ------------------------------
  For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15404&LinkId=20476

  ------------------------------
  Server Name: xxxxx
  Error Number: 15404
  Severity: 16
  State: 19
  Procedure: xp_logininfo
  Line Number: 60

  
  ------------------------------
  Program Location:

     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
     at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
     at System.Data.SqlClient.SqlDataReader.get_MetaData()
     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
     at System.Data.SqlClient.SqlCommand.ExecuteReader()
     at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)

  This is where I get stuck and am unable to proceed ahead :(

  
  

• Mike Torres

  ok.. here's a development...

  i restarted my machine and explicitly added my domain login credentials to the SQL Login. Post that at the publication access list proprty page i was able to add the IUSR_ and my domain credentials (no error this time)..

  post this running the SQL Agent again threw the same error as before.

  then i went to the SQL Server Configuration Manager and changed the credential under which the agent was running from network service to my domain credential. upon restarting the service everything was exectued fine and now i have my snap shot!

  thanks for all the help that you provided Darren. I am extremely grateful.

  
  

• Chris4510

  Assuming you are using SQL Server 2005 Management Studio, the first thing you want to do is add a login to the published database (not the publication just yet) for IUSR_{machine_name}.  Give it DataReader and DataWriter permissions on the db.

  Once this IUSR account is a valid login, then right click on your replication pulication and on the Publication Access List property page, click Add.  Now the IUSR account will show up as an account that can be granted permissions on the publication.

  Again, once these steps are complete, regenerate your snapshot (right click on the publication and choose option to View Snapshot Agent Status then click the button for Generate Snapshot).  It can take a few minutes to run if there is a lot of data in the published database, so be patient.  If you want to see the results, you can open Replication Monitor and double-check that the snapshot was generated successfully.

  If you still get an error when attempting to replicate from the device, let me know the exception HRESULT. 

  thanks,

  Darren

   

  
  
  

• Kaan Demirtas

  I've never seen that happen and I've been using SQL Server 2005 since beta 1.  I suspect we may need to ask this question over in the SQL Server 2005 forum - the publication property pages throwing an exception is obviously not an expected behavior.  If it were me, I would reinstall SQL Server 2005 just to be safe.

  -Darren

   

  
  
  
• SQL Mobile Query