This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

AppInsight for SQL credentials

Hello Everyone!

I'm starting this thread because most of my SQL database servers, which are added into NPM & SAM won't accept their credentials. Here's the situation explained:

After adding the "AppInsight for SQL" monitor to my SQL database servers, the servers and SQL instances are visibile on the Orion interface tab "Application". But all of them have a grey LED. This is obviously because i need to provide the credentials so that Orion can monitor the SQL instance correctly.

When providing the "SQL database instance login-allowed" credentials, the credential test "Fails". No matter what credentials I use. The credentials i've tried have domain admin rights, SQL sysadmin rights and I even tried with dba owners. None of  them work.

The funny thing is. I've also got a SQL server which DOES accept it's credentials. and all of the above SQL credentials i've tested with work correctly. So i've tried to see what the difference is between the servers who do and don't accept the SQL credentials. But I could'nt find any logical explanation.

However I am interested in the log file, but I don't know which and where i can't find it.

For the record: Both working and not working server have the same: SQL -> Security -> Logins -> accounts and rights.

Some info on the tested servers:

Correctly working server:

Win server 2008 R2 Enterprise SP1

MS SQL Server 2008 R2

MS SQL Server Management Studio 10.50.1600.1

(SQL) Operating System 6.1.7601

Not working server:

Win Server 2008 R2 Enterprise SP1

MS SQL Server 2008 R2

MS SQL Server Management studio 10.50.4000.0

(SQL) Operation System 6.1.7601

I really don't know what could cause this problem. So if anyone could help me out here?


  • I am assuming you have looked at page 5 and 6 already to create an account with the necessary permissions

    http://www.solarwinds.com/documentation/apm/docs/SAMAppInsightGuide.pdf

    Have you tried one of the 'older templates' like this one just to test if the SQL client has no issues communicating to the SQL server not working for AppInsight?

    SQL Server 2005-2008 Performance (SQL)

  • Hi HolyGuacamole,

    The testuser has db owner and domain admin rights so that should be okay. You're template helped me out a bit. Because it gave me a more detailed error message:

    Testing on node node1.domain.com: failed with 'Down' status.
    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

    I just looked at the firewall and UAC configs of both the working and not working server. They are not the same:

    Correctly configured server:

    UAC: Off

    Firewall:

    Block all connections to programs that are not on the list of allowed programs

    Not accepting credentials server:

    UAC: On

    Firewall:

    Allow all connections to programs that do not have an exception to block the connection

    Could this be it?

  • I've turned off the UAC setting. Without any change in the result.

  • UAC does cause some very mysterious situations. It is a possibility but UAC settings do require a reboot to take effect.

    we need to narrow down at what level (ip, ports, application, etc). The steps outlined below are pretty good to try

    Steps to troubleshoot SQL connectivity issues - SQL Protocols - Site Home - MSDN Blogs

  • It sounds like you may have assigned AppInsight for SQL manually to the node. If that's the case, please remove the application(s) and enable AppInsight for SQL for the node using "List Resources"

    Add Node - AppInsight.png

  • Hi,


    Thank you for you're answer but that's not it. I've added it by using the way "resource tab" you meantioned. That way it will indeed auto-detect the SQL Server including the correct instance.

    Also i've tried the following:

    USE masterGRANT VIEW SERVER STATE TO "Viecuri\sqladmin"GRANT VIEW ANY DEFINITION TO "Viecuri\sqladmin"EXEC sp_adduser @loginame = 'Viecuri\sqladmin' ,@name_in_db = 'Viecuri\sqladmin'GRANT EXECUTE ON xp_readerrorlog TO "Viecuri\sqladmin"USE msdbEXEC sp_adduser @loginame = 'Viecuri\sqladmin' ,@name_in_db = 'Viecuri\sqladmin'EXEC sp_addrolemember N'db_datareader',N'Viecuri\sqladmin'EXECUTE sp_MSforeachdb 'USE [?]; EXEC sp_adduser @loginame =''Viecuri\sqladmin'', @name_in_db =''Viecuri\sqladmin'''

    This query solved the problem for 1 of the 30 SQL servers. So most of them still won't work correctly.

    i'm going to try out the guacamole way:

    we need to narrow down at what level (ip, ports, application, etc).

    i'll let you know if this changes anything

  • The account you created in the steps above are a SQL user account. If the SQL servers you are trying to monitor are not running in mixed mode then this could be the issue. Have you tried using the local or domain Windows user account in the domain\username format to authenticate against any of these SQL instances?  Have you verified that these SQL instances are available on the Network and not using shared memory as their method of connecting/authenticating? Named Pipes and/or TCP/IP should be enabled on the SQL server and bound to the network interface, not the loopback.

  • As you can see the account above is addressed as domain\user. This is because the user SQLadmin is a domain user.


    The SQL server and instance running on it is a node which is part of a 2-node cluster. It's a production environment SQL cluster server. So it does have TCP/IP.


    The SQL servers are running mixed-mode.

  • Note that AppInsight for SQL should not be assigned to the SQL Server itself when operating in a cluster. AppInsight for SQL should only be assigned to the Cluster itself via a node that represents the VIP, not each cluster member individually.

  • If that's correct, we've found the solution. I'll try it right away