When a Microsoft SQL connection is attempted, I get the following errors. I am trying to connect to a names instance of MS SQL over TCP 1785. Any ideas? Thanks.... Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error running SQL query; type='freetds', host='sql.hidden.local\instance', port='1785', user='user', database='syslog', error='', query='CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096))' Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error creating table, giving up; table='syslogng' Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error checking table, disconnecting from database, trying again shortly; time_reopen='60' Shawn Cannon IT Professional http://www.shawncannon.com Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19>
On Monday, December 13, 2010 07:43:05 Shawn Cannon wrote:
When a Microsoft SQL connection is attempted, I get the following errors. I am trying to connect to a names instance of MS SQL over TCP 1785. Any ideas? Thanks....
Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error running SQL query; type='freetds', host='sql.hidden.local\instance', port='1785', user='user', database='syslog', error='', query='CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096))' Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error creating table, giving up; table='syslogng' Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error checking table, disconnecting from database, trying again shortly; time_reopen='60'
Shawn Cannon IT Professional
Hello Shawn, The first step for any DB connection is verifying that the same exact statements work right when executed by hand using the same user and password provided in the configuration to eliminate variables. On Windows you could do this using Toad, SQL Server Admin Console, etc. On UNIX the client for MS SQL is called sqsh, pronounced "squish": http://packages.ubuntu.com/maverick/sqsh This link describes the Ubuntu package. Same idea applies to Debian / RH / SuSE / etc. In this case I assume your database user has a privileges issue. So you better take a look at the grants for that user. Good Luck, Matthew.
Hi, First of all, I don't think that "host='sql.hidden.local\instance'" will work, but I've never tried it yet. Secondly, there is a known bug in syslog-ng, it does not show the received error message from freetds that's why you don't see any real error message. If you want to try it manually, you should install freetds on your computer (e.g.: apt-get install freetds on Ubuntu), than you can connect to mssql database using "tsql" command. Just execute the following commands after connecting mssql database with tsql command: CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096)); go If it works, the problem is the host\instance form. If it doesn't, freetds will give you a proper error message about the reason of the problem. 2010.12.13. 16:43 keltezéssel, Shawn Cannon írta:
When a Microsoft SQL connection is attempted, I get the following errors. I am trying to connect to a names instance of MS SQL over TCP 1785. Any ideas? Thanks....
Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error running SQL query; type='freetds', host='sql.hidden.local\instance', port='1785', user='user', database='syslog', error='', query='CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096))' Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error creating table, giving up; table='syslogng' Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error checking table, disconnecting from database, trying again shortly; time_reopen='60'
Shawn Cannon
IT Professional
http://www.shawncannon.com <http://www.shawncannon.com/>
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook <http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later… <http://www.shawncannon.com/?p=19>
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
-- pzolee
OK I installed it ans ran TDS using sql.hidden.local and the port number and I was able to create the table. I changed my syslogng config file to this same setting, yet it still gives me an error. I left the table in the database that was created but even that did not help. Any ideas? Shawn Cannon IT Professional http://www.shawncannon.com Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19> On Mon, Dec 13, 2010 at 11:36 AM, Zoltán Pallagi <pzolee@balabit.hu> wrote:
Hi,
First of all, I don't think that "host='sql.hidden.local\instance'" will work, but I've never tried it yet. Secondly, there is a known bug in syslog-ng, it does not show the received error message from freetds that's why you don't see any real error message.
If you want to try it manually, you should install freetds on your computer (e.g.: apt-get install freetds on Ubuntu), than you can connect to mssql database using "tsql" command. Just execute the following commands after connecting mssql database with tsql command: CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096)); go
If it works, the problem is the host\instance form. If it doesn't, freetds will give you a proper error message about the reason of the problem.
2010.12.13. 16:43 keltezéssel, Shawn Cannon írta:
When a Microsoft SQL connection is attempted, I get the following errors. I am trying to connect to a names instance of MS SQL over TCP 1785. Any ideas? Thanks....
Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error running SQL query; type='freetds', host='sql.hidden.local\instance', port='1785', user='user', database='syslog', error='', query='CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096))' Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error creating table, giving up; table='syslogng' Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error checking table, disconnecting from database, trying again shortly; time_reopen='60'
Shawn Cannon
IT Professional
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19>
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
-- pzolee
I meant I ran tsql. :-) Shawn Cannon IT Professional http://www.shawncannon.com Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19> On Mon, Dec 13, 2010 at 12:12 PM, Shawn Cannon <shawn@shawncannon.com>wrote:
OK I installed it ans ran TDS using sql.hidden.local and the port number and I was able to create the table. I changed my syslogng config file to this same setting, yet it still gives me an error. I left the table in the database that was created but even that did not help. Any ideas?
Shawn Cannon
IT Professional
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19>
On Mon, Dec 13, 2010 at 11:36 AM, Zoltán Pallagi <pzolee@balabit.hu>wrote:
Hi,
First of all, I don't think that "host='sql.hidden.local\instance'" will work, but I've never tried it yet. Secondly, there is a known bug in syslog-ng, it does not show the received error message from freetds that's why you don't see any real error message.
If you want to try it manually, you should install freetds on your computer (e.g.: apt-get install freetds on Ubuntu), than you can connect to mssql database using "tsql" command. Just execute the following commands after connecting mssql database with tsql command: CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096)); go
If it works, the problem is the host\instance form. If it doesn't, freetds will give you a proper error message about the reason of the problem.
2010.12.13. 16:43 keltezéssel, Shawn Cannon írta:
When a Microsoft SQL connection is attempted, I get the following errors. I am trying to connect to a names instance of MS SQL over TCP 1785. Any ideas? Thanks....
Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error running SQL query; type='freetds', host='sql.hidden.local\instance', port='1785', user='user', database='syslog', error='', query='CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096))' Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error creating table, giving up; table='syslogng' Dec 13 10:33:23 syslog01 syslog-ng[27963]: Error checking table, disconnecting from database, trying again shortly; time_reopen='60'
Shawn Cannon
IT Professional
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19>
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
-- pzolee
On Mon, Dec 13, 2010 at 12:19:03PM -0500, Shawn Cannon wrote:
I meant I ran tsql. :-)
Shawn Cannon
IT Professional
OK I installed it ans ran TDS using sql.hidden.local and the port number and I was able to create the table. I changed my syslogng config file to this same setting, yet it still gives me an error. I left the table in the database that was created but even that did not help. Any ideas?
There have been some bugs in the past where syslog-ng was not always connecting to the database correctly on the proper ports, etc. However in your case this could only apply if something was also listening on the default port I would assume, since it seemed like it had connected. Although as one of the other posters had pointed out there could be an inaccurate error message as well. Perhaps you could verify it's connecting properly to the DB using Wireshark? Was the tsql / sqsh / etc. based testing performed using the same machine as the syslog-ng setup is running? Matthew.
Would it help if I posted the syntax of my config file? Shawn Cannon IT Professional http://www.shawncannon.com Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19> On Mon, Dec 13, 2010 at 1:17 PM, Matthew Hall <mhall@mhcomputing.net> wrote:
On Mon, Dec 13, 2010 at 12:19:03PM -0500, Shawn Cannon wrote:
I meant I ran tsql. :-)
Shawn Cannon
IT Professional
OK I installed it ans ran TDS using sql.hidden.local and the port number and I was able to create the table. I changed my syslogng config file to this same setting, yet it still gives me an error. I left the table in the database that was created but even that did not help. Any ideas?
There have been some bugs in the past where syslog-ng was not always connecting to the database correctly on the proper ports, etc. However in your case this could only apply if something was also listening on the default port I would assume, since it seemed like it had connected. Although as one of the other posters had pointed out there could be an inaccurate error message as well. Perhaps you could verify it's connecting properly to the DB using Wireshark?
Was the tsql / sqsh / etc. based testing performed using the same machine as the syslog-ng setup is running?
Matthew.
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
OK this is some good info. It looks like even though I specify the database name, it is someone how using it and tries to create or check for the tables on the master database. Any ideas? Here is the info from the capture. Why is is running a SELECT looking for syslogng instead of the database name? *....(....SELECT * FROM syslogng WHERE 0=1...K.<...7.........Invalid object name 'syslogng'..RAWSQL\MARTY....................CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096))...`.<...L.......4.CREATE TABLE permission denied in database 'master'..RAWSQL\MARTY............ * Here is my destination section from my syslogng config: destination d_mssql { sql(type(mssql) host("rawsql") port("1785") username("*******") password("*******") database("Database") table("syslogng")columns("datetime varchar(16)", "host varchar(32)", "program varchar(32)", "pid varchar(8)", "message varchar(4096)") values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSGONLY") indexes("datetime", "host", "program", "pid")); }; Shawn Cannon IT Professional http://www.shawncannon.com Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19> On Mon, Dec 13, 2010 at 1:45 PM, Shawn Cannon <shawn@shawncannon.com> wrote:
Would it help if I posted the syntax of my config file?
Shawn Cannon
IT Professional
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19>
On Mon, Dec 13, 2010 at 1:17 PM, Matthew Hall <mhall@mhcomputing.net>wrote:
On Mon, Dec 13, 2010 at 12:19:03PM -0500, Shawn Cannon wrote:
I meant I ran tsql. :-)
Shawn Cannon
IT Professional
OK I installed it ans ran TDS using sql.hidden.local and the port number and I was able to create the table. I changed my syslogng config file to this same setting, yet it still gives me an error. I left the table in the database that was created but even that did not help. Any ideas?
There have been some bugs in the past where syslog-ng was not always connecting to the database correctly on the proper ports, etc. However in your case this could only apply if something was also listening on the default port I would assume, since it seemed like it had connected. Although as one of the other posters had pointed out there could be an inaccurate error message as well. Perhaps you could verify it's connecting properly to the DB using Wireshark?
Was the tsql / sqsh / etc. based testing performed using the same machine as the syslog-ng setup is running?
Matthew.
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
On Mon, Dec 13, 2010 at 02:23:18PM -0500, Shawn Cannon wrote:
OK this is some good info. It looks like even though I specify the database name, it is someone how using it and tries to create or check for the tables on the master database. Any ideas? Here is the info from the capture. Why is is running a SELECT looking for syslogng instead of the database name?
For this we will probably need help from the syslog-ng team proper. It could take a couple of days since they are in Europe / CET Zone. Meanwhile, please read over this chapter from the manual to see if you can work around the problem somehow: http://www.balabit.com/sites/default/files/documents/syslog-ng-ose-v3.2-guid... 3.6. Configuring Microsoft SQL Server to accept logs from syslog-ng There have been some glitches in the support for DB configuration before. Such as port numbers being ignored and hard coded to defaults by mistake. This happens because libdbi is a third party library that's not part of the core code. Matthew.
Well, syslog-ng tries to query the result of "select * from tablename where 0=1;" to check if the specified table exists. If it didn't return with error, then the table exists, and syslog-ng will check the columns and alter the table if needed. In other cases (the above query returned with any error), syslog-ng assumes that the specified table does not exist and tries to create it. As far as I see, this query returned with error in your case, that's the reason why syslog-ng tries to create it after reaching the next time_reopen. Also, it seems that syslog-ng has not enough permission to create this table. Are you sure that the user specified in syslog-ng conf has the sufficient permissions for this database? You have just told me that you tried it with tsql, could you show me the output of this? Have you used the same user? I mean you should execute tsql with the following parameters: tsql -S rawsql -p 1785 -U user_in_conf -P password_in_conf 2010.12.13. 20:23 keltezéssel, Shawn Cannon írta:
OK this is some good info. It looks like even though I specify the database name, it is someone how using it and tries to create or check for the tables on the master database. Any ideas? Here is the info from the capture. Why is is running a SELECT looking for syslogng instead of the database name?
*....(....SELECT * FROM syslogng WHERE 0=1...K.<...7.........Invalid object name 'syslogng'..RAWSQL\MARTY....................CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096))...`.<...L.......4.CREATE TABLE permission denied in database 'master'..RAWSQL\MARTY............ * Here is my destination section from my syslogng config:
destination d_mssql { sql(type(mssql) host("rawsql") port("1785") username("*******") password("*******") database("Database") table("syslogng")columns("datetime varchar(16)", "host varchar(32)", "program varchar(32)", "pid varchar(8)", "message varchar(4096)") values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSGONLY") indexes("datetime", "host", "program", "pid")); };
Shawn Cannon
IT Professional
http://www.shawncannon.com <http://www.shawncannon.com/>
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook <http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later… <http://www.shawncannon.com/?p=19>
On Mon, Dec 13, 2010 at 1:45 PM, Shawn Cannon <shawn@shawncannon.com <mailto:shawn@shawncannon.com>> wrote:
Would it help if I posted the syntax of my config file?
Shawn Cannon
IT Professional
http://www.shawncannon.com <http://www.shawncannon.com/>
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook <http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later… <http://www.shawncannon.com/?p=19>
On Mon, Dec 13, 2010 at 1:17 PM, Matthew Hall <mhall@mhcomputing.net <mailto:mhall@mhcomputing.net>> wrote:
On Mon, Dec 13, 2010 at 12:19:03PM -0500, Shawn Cannon wrote: > I meant I ran tsql. :-) > > Shawn Cannon > > IT Professional > > > OK I installed it ans ran TDS using sql.hidden.local and the port number > > and I was able to create the table. I changed my syslogng config file to > > this same setting, yet it still gives me an error. I left the table in the > > database that was created but even that did not help. Any ideas?
There have been some bugs in the past where syslog-ng was not always connecting to the database correctly on the proper ports, etc. However in your case this could only apply if something was also listening on the default port I would assume, since it seemed like it had connected. Although as one of the other posters had pointed out there could be an inaccurate error message as well. Perhaps you could verify it's connecting properly to the DB using Wireshark?
Was the tsql / sqsh / etc. based testing performed using the same machine as the syslog-ng setup is running?
Matthew. ______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
-- pzolee
The only thing that I did not have selected for the sql user was to default it to the database I wanted to use. It defaulted to Master. I changed that and now all is well. Kinda strange to have an option called database() if it does not use that. Shawn Cannon IT Professional http://www.shawncannon.com Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19> On Mon, Dec 13, 2010 at 4:29 PM, Zoltán Pallagi <pzolee@balabit.hu> wrote:
Well, syslog-ng tries to query the result of "select * from tablename where 0=1;" to check if the specified table exists. If it didn't return with error, then the table exists, and syslog-ng will check the columns and alter the table if needed. In other cases (the above query returned with any error), syslog-ng assumes that the specified table does not exist and tries to create it. As far as I see, this query returned with error in your case, that's the reason why syslog-ng tries to create it after reaching the next time_reopen. Also, it seems that syslog-ng has not enough permission to create this table. Are you sure that the user specified in syslog-ng conf has the sufficient permissions for this database?
You have just told me that you tried it with tsql, could you show me the output of this? Have you used the same user? I mean you should execute tsql with the following parameters: tsql -S rawsql -p 1785 -U user_in_conf -P password_in_conf
2010.12.13. 20:23 keltezéssel, Shawn Cannon írta:
OK this is some good info. It looks like even though I specify the database name, it is someone how using it and tries to create or check for the tables on the master database. Any ideas? Here is the info from the capture. Why is is running a SELECT looking for syslogng instead of the database name?
*....(....SELECT * FROM syslogng WHERE 0=1...K.<...7.........Invalid object name 'syslogng'..RAWSQL\MARTY....................CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096))...`.<...L.......4.CREATE TABLE permission denied in database 'master'..RAWSQL\MARTY............ * Here is my destination section from my syslogng config:
destination d_mssql { sql(type(mssql) host("rawsql") port("1785") username("*******") password("*******") database("Database") table("syslogng")columns("datetime varchar(16)", "host varchar(32)", "program varchar(32)", "pid varchar(8)", "message varchar(4096)") values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSGONLY") indexes("datetime", "host", "program", "pid")); };
Shawn Cannon
IT Professional
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19>
On Mon, Dec 13, 2010 at 1:45 PM, Shawn Cannon <shawn@shawncannon.com>wrote:
Would it help if I posted the syntax of my config file?
Shawn Cannon
IT Professional
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn<http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook<http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later…<http://www.shawncannon.com/?p=19>
On Mon, Dec 13, 2010 at 1:17 PM, Matthew Hall <mhall@mhcomputing.net>wrote:
On Mon, Dec 13, 2010 at 12:19:03PM -0500, Shawn Cannon wrote:
I meant I ran tsql. :-)
Shawn Cannon
IT Professional
OK I installed it ans ran TDS using sql.hidden.local and the port number and I was able to create the table. I changed my syslogng config file to this same setting, yet it still gives me an error. I left the table in the database that was created but even that did not help. Any ideas?
There have been some bugs in the past where syslog-ng was not always connecting to the database correctly on the proper ports, etc. However in your case this could only apply if something was also listening on the default port I would assume, since it seemed like it had connected. Although as one of the other posters had pointed out there could be an inaccurate error message as well. Perhaps you could verify it's connecting properly to the DB using Wireshark?
Was the tsql / sqsh / etc. based testing performed using the same machine as the syslog-ng setup is running?
Matthew.
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
-- pzolee
On Mon, Dec 13, 2010 at 05:04:07PM -0500, Shawn Cannon wrote:
Kinda strange to have an option called database() if it does not use that.
This is open source software. MS SQL is a proprietary non UNIX DB. So there will be bugs in this because it's reverse engineered code that's not maintained directly as part of syslog-ng. Please contribute to the community by writing a good bug report.
Shawn Cannon
Regards, Matthew.
Hm... You are right, it seems that syslog-ng tries to connect to the database that is the default for the specified user in config. However, I've checked it in syslog-ng source, and syslog-ng uses the same code for all database types for connecting and passes the name of database properly and in case of other databases it works, too. So, it is a bug, but not in syslog-ng but in freetds or libdbi. Report a bug on it, please, and we will check if we can fix it. Thanks On 2010-12-13 23:04, Shawn Cannon wrote:
The only thing that I did not have selected for the sql user was to default it to the database I wanted to use. It defaulted to Master. I changed that and now all is well. Kinda strange to have an option called database() if it does not use that.
Shawn Cannon
IT Professional
http://www.shawncannon.com <http://www.shawncannon.com/>
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook <http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later… <http://www.shawncannon.com/?p=19>
On Mon, Dec 13, 2010 at 4:29 PM, Zoltán Pallagi <pzolee@balabit.hu <mailto:pzolee@balabit.hu>> wrote:
Well, syslog-ng tries to query the result of "select * from tablename where 0=1;" to check if the specified table exists. If it didn't return with error, then the table exists, and syslog-ng will check the columns and alter the table if needed. In other cases (the above query returned with any error), syslog-ng assumes that the specified table does not exist and tries to create it. As far as I see, this query returned with error in your case, that's the reason why syslog-ng tries to create it after reaching the next time_reopen. Also, it seems that syslog-ng has not enough permission to create this table. Are you sure that the user specified in syslog-ng conf has the sufficient permissions for this database?
You have just told me that you tried it with tsql, could you show me the output of this? Have you used the same user? I mean you should execute tsql with the following parameters: tsql -S rawsql -p 1785 -U user_in_conf -P password_in_conf
2010.12.13. 20:23 keltezéssel, Shawn Cannon írta:
OK this is some good info. It looks like even though I specify the database name, it is someone how using it and tries to create or check for the tables on the master database. Any ideas? Here is the info from the capture. Why is is running a SELECT looking for syslogng instead of the database name?
*....(....SELECT * FROM syslogng WHERE 0=1...K.<...7.........Invalid object name 'syslogng'..RAWSQL\MARTY....................CREATE TABLE syslogng (datetime varchar(16), host varchar(32), program varchar(32), pid varchar(8), message varchar(4096))...`.<...L.......4.CREATE TABLE permission denied in database 'master'..RAWSQL\MARTY............ * Here is my destination section from my syslogng config:
destination d_mssql { sql(type(mssql) host("rawsql") port("1785") username("*******") password("*******") database("Database") table("syslogng")columns("datetime varchar(16)", "host varchar(32)", "program varchar(32)", "pid varchar(8)", "message varchar(4096)") values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSGONLY") indexes("datetime", "host", "program", "pid")); };
Shawn Cannon
IT Professional
http://www.shawncannon.com <http://www.shawncannon.com/>
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook <http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later… <http://www.shawncannon.com/?p=19>
On Mon, Dec 13, 2010 at 1:45 PM, Shawn Cannon <shawn@shawncannon.com <mailto:shawn@shawncannon.com>> wrote:
Would it help if I posted the syntax of my config file?
Shawn Cannon
IT Professional
http://www.shawncannon.com <http://www.shawncannon.com/>
Contact Me <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> LinkedIn <http://www.linkedin.com/pub/shawn-cannon/5/84/81a> <http://www.facebook.com/rolltidega72> Facebook <http://www.facebook.com/rolltidega72> <http://twitter.com/rolltidega> Twitter <http://twitter.com/rolltidega> My blog: My thoughts on VMworld… a week later… <http://www.shawncannon.com/?p=19>
On Mon, Dec 13, 2010 at 1:17 PM, Matthew Hall <mhall@mhcomputing.net <mailto:mhall@mhcomputing.net>> wrote:
On Mon, Dec 13, 2010 at 12:19:03PM -0500, Shawn Cannon wrote: > I meant I ran tsql. :-) > > Shawn Cannon > > IT Professional > > > OK I installed it ans ran TDS using sql.hidden.local and the port number > > and I was able to create the table. I changed my syslogng config file to > > this same setting, yet it still gives me an error. I left the table in the > > database that was created but even that did not help. Any ideas?
There have been some bugs in the past where syslog-ng was not always connecting to the database correctly on the proper ports, etc. However in your case this could only apply if something was also listening on the default port I would assume, since it seemed like it had connected. Although as one of the other posters had pointed out there could be an inaccurate error message as well. Perhaps you could verify it's connecting properly to the DB using Wireshark?
Was the tsql / sqsh / etc. based testing performed using the same machine as the syslog-ng setup is running?
Matthew. ______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
______________________________________________________________________________ Member info:https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation:http://www.balabit.com/support/documentation/?product=syslog-ng FAQ:http://www.campin.net/syslog-ng/faq.html
-- pzolee
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
Yes it was the same machine. I will load wireshark and see. Shawn Cannon | NW Systems Administrator, Sr. SSCP, MCITP, VCP3/4, EMCISA Gladiator Technology, A ProfitStars® Solution 11395 Old Roswell Rd | Alpharetta, GA 30009 Office: (877) GLADTECH x458190 | Fax: (678) 461-4625 Direct: (678) 242-4902 | Mobile: (678) 689-3700 Http://www.gladiatortechnology.com -----Original message----- From: Matthew Hall <mhall@mhcomputing.net> To: Syslog-ng users' and developers' mailing list <syslog-ng@lists.balabit.hu> Sent: Mon, Dec 13, 2010 18:17:38 GMT+00:00 Subject: Re: [syslog-ng] Microsoft SQL connect issue On Mon, Dec 13, 2010 at 12:19:03PM -0500, Shawn Cannon wrote:
I meant I ran tsql. :-)
Shawn Cannon
IT Professional
OK I installed it ans ran TDS using sql.hidden.local and the port number and I was able to create the table. I changed my syslogng config file to this same setting, yet it still gives me an error. I left the table in the database that was created but even that did not help. Any ideas?
There have been some bugs in the past where syslog-ng was not always connecting to the database correctly on the proper ports, etc. However in your case this could only apply if something was also listening on the default port I would assume, since it seemed like it had connected. Although as one of the other posters had pointed out there could be an inaccurate error message as well. Perhaps you could verify it's connecting properly to the DB using Wireshark? Was the tsql / sqsh / etc. based testing performed using the same machine as the syslog-ng setup is running? Matthew. ______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
participants (3)
-
Matthew Hall
-
Shawn Cannon
-
Zoltán Pallagi