MSSQL connection through SPS it not working with Windows authentication

Hi,

I am trying to connect SQL management studio to a SQL server through MSSQL connection of SPS.

The connection using the Windows authentication is not working giving error 

No username specified
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (.Net SqlClient Data Provider)

when using the sql authentication it is working fine.

did any one receive this issue or how to fix this.may be i am missing some simple config

Best Regards,

Abubakar

Parents
  • Hi Abubakar

    I cannot give you a definitive answer here and I have very limited knowledge of SQL but based on what you are describing and my knowledge of SPP I would hazard an educated guess that what you are seeing is by design.

    When you select the platform for the system you wish to manage this defines the way SPP communicates with that target system.

    So I am guessing that you have defined and SQL platform against the target system and that from your description this is working and It will be using SQL authentication to carry out management tasks.

    So I suspect that when  you try to use Windows authentication it is still using SQL authentication and so fails.

    Maybe you could try defining another target system this time selecting a Windows platform type for your SQL server and see if this works. My thinking is that this will then try to authenticate to the target server using Windows authentication rather than SQL authentication.

    If this does not work, I an sure that there are other on this forum who have greater knowledge of SQL operations than me who will be able to give advice.

    Good luck

    Best regards

    Tim

  • Hello Tim

    thanks for you response

    I am here SPS not SPP. this feature was introduce recently in SPS where you can record or keep the audit trail of the session of SQL server management studio.

    The connection policy created in SPS is of type MSSQL. it is working fine when i am connecting Management studio with SQL auth but when i select it with windows auth it is giving and error. May be there is some parameter in SPS connection policy which can solve this but i am also figuring it out.

Reply
  • Hello Tim

    thanks for you response

    I am here SPS not SPP. this feature was introduce recently in SPS where you can record or keep the audit trail of the session of SQL server management studio.

    The connection policy created in SPS is of type MSSQL. it is working fine when i am connecting Management studio with SQL auth but when i select it with windows auth it is giving and error. May be there is some parameter in SPS connection policy which can solve this but i am also figuring it out.

Children
  • Hi Abubakarsh

    I am sorry but I am still learning about SPS as well so I cannot confirm if there are any specific setting that could effect what you are looking at.

    That said, are you using the SPP - SPS "Join" feature to  retrieve the credentials from SPP to start the session? If you are then I would look to see what account credentials SPS is retrieving from SPP to make sure that the correct account and credentials are being retrieved.

    If not using the join feature or the correct account credentials are being retrieved, then I would look to the SPS policy that is responsible for the SQL connection to see if there is anything in there that will help.

    I am sure that one of the SPS team will be able to give you advice here.

    Best regards

    Tim