3 Replies Latest reply on Mar 7, 2012 4:44 PM by Garrett Gross

    Interfacing storage profiler with reporting services

    rkelly

      Hello,

      I've looked but not found a thread that addresses this question, so I apologise if this exists already.

      We've installed storage profiler on a VM and we;d like to interface microsoft reporting services directly to the mysql database. I understand that the preferred way to do this is to set up a service account that matches a read only account in that needs to be created on the mysql database.

      I'm database challenged so... is this the correct approach and can I get some pointers on how this should be done. Reporting services sits on a different server and will be using ODBC to connect to the Storage Profiler MySQL database.

      Thanks

        • Re: Interfacing storage profiler with reporting services
          cshanep

          I have been looking at this problem and do not have a good solution as far as connecting SSRS to MySQL database. My issue is once I have made a connection to the database after creating a query, I get an error because SSRS tries to add [] brackets that MySQL does not support while creating the data souce view.

          With that said, I have been successful in getting data from MySQL into MS SQL Server using SSIS. To do that, you can do the following:

          1. Edit the my.cnf file in the for the MySQL database. You will need to change the bind variable to the IP of the machine with MySQL database instead of the localhost address.

          2. Create a user in the MySQL database with select privileges on the Profiler database.

          3. Install the MySQL connector on the SQL Server machine (more information here: http://blogs.msdn.com/b/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx).

          4. Build your SSIS package (with your MySQL queries, bringing the data over to MS SQL server database) and run it.

          Once the data has been brought over into MS SQL Server, you can easily connect to the MS SQL Server using SSRS.

          I hope this helps!

            • Re: Interfacing storage profiler with reporting services
              bpocooks

              I have used the MYSQL ODBC connector in the past and created a linked server that pulls from Profiler v 4.x. With an upgrade to v5.x by our staff on  new system, I found that I could no longer create a user and grant them rights. The default admin user does not have the GRANT privilege in the MYSQL DB so it is not possible to access the data remotely even for the admin user. Did you use v 5.x to do this or the older versions. I used OPENQUERY statements to pull in SQL and then could work with the data easliy on the fly. Worked great in SSRS 2008.

            • Re: Interfacing storage profiler with reporting services
              Garrett Gross

              The process that I am familiar with for any kind of remote MySQL administration is as follows: 

               

              You’re going to want to get to <installdir>/mysql/bin

              From here you’re going to need to give the admin user the permission to add the new user you need (replace solarwinds with your current database password):

              ./mysql –u admin –p solarwinds –D mysql –e “UPDATE USER SET GRANT_PRIV='Y' where USER='admin'”

              ./mysql –u admin –p solarwinds –D mysql –e “FLUSH PRIVILEGES”

               

              To create a read only user (replace remotereadonly with the username of your choice and password with the password of your choice. If you wish to specify what hostname the user has to come from replace the % with the hostname):

              ./mysql –u admin –p solarwinds –D mysql –e "GRANT SELECT ON storage.* TO 'remotereadonly'@'%' IDENTIFIED BY 'password'"

              ./mysql –u admin –p solarwinds –D mysql –e “FLUSH PRIVILEGES”

              To create a read admin user (replace remoteadmin with the username of your choice and password with the password of your choice. If you wish to specify what hostname the user has to come from replace the % with the hostname):

               

              ./mysql –u admin –p solarwinds –D mysql –e "GRANT ALL PRIVILEGES ON *.* TO 'remoteadmin'@'%' IDENTIFIED BY 'password'"

              ./mysql –u admin –p solarwinds –D mysql –e “FLUSH PRIVILEGES” 

               

              Let me know if this helps.