You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by "O. Olson" <ol...@yahoo.it> on 2013/01/23 22:32:53 UTC

Solr SQL Express Integrated Security - Unable to execute query

Hi,

	I am using the /example-DIH in the Solr 4.0 download. The example worked
out of the box using the HSQLDB. I then attempted to modify the files to
connect to a SQL Express instance running on my local machine. A
http://localhost:8983/solr/db/dataimport?command=full-import results in 

org.apache.solr.common.SolrException log
SEVERE: Full Import failed:java.lang.RuntimeException:
java.lang.RuntimeException:
org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to
execute query: SELECT [ProdID],[Descr] FROM [Amazon].[dbo].[Table_Temp]
Processing Document # 1
	at
org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:273) …

I first copied sqljdbc4.jar (from Microsoft)  to
/example/example-DIH/solr/db/lib. I have the following db-data-config.xml:

<dataConfig>
    <dataSource driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;databaseName=Amazon;integratedSecurity=true;"/>
    <document>
        <entity name="Product" 
				query="SELECT [ProdID],[Descr] FROM [Amazon].[dbo].[Table_Temp]">
            <field column="ProdID" name="ProdID" />
            <field column="Descr" name="Descr" />
        </entity>
    </document>
</dataConfig>

I have adjusted my schema.xml file accordingly.

Is there anyway I can debug this problem? I want to use Integrated
Security/Authentication, am I doing this correctly?

Thank you for all the help.
O. O.




--
View this message in context: http://lucene.472066.n3.nabble.com/Solr-SQL-Express-Integrated-Security-Unable-to-execute-query-tp4035758.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: Solr SQL Express Integrated Security - Unable to execute query

Posted by "O. Olson" <ol...@yahoo.it>.
Michael Della Bitta-2 wrote
> On Thu, Jan 24, 2013 at 11:34 AM, O. Olson &lt;

> olson_ord@

> &gt; wrote:
>>
>> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The server
>> SQLEXPRESS is not configured to listen with TCP/IP.
> 
> 
> That's probably your problem...
> 
> 
> Michael Della Bitta
> 
> ------------------------------------------------
> Appinions
> 18 East 41st Street, 2nd Floor
> New York, NY 10017-6271
> 
> www.appinions.com
> 
> Where Influence Isn’t a Game

============================
Good call Michael. I did have to enable TCP
(http://msdn.microsoft.com/en-us/library/hh231672.aspx  for others who have
the same problem), but I did not still not get this to work. 

I then tested my Driver, JDBC URL & SQL Query in a plain old Java class.
This showed me that it was almost impossible to get integrated
authentication to work in Java. I finally went with specifying the usename
and password literally. (I hope this useful to others):


public static void main(String[] args) throws Exception {
		String url =
"jdbc:sqlserver://localhost\\SQLEXPRESS;database=Amazon;user=solrusr;password=solrusr;";
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		Connection connection = null;
		try {
			System.out.println("Loading driver...");
			Class.forName(driver);
			System.out.println("Driver loaded! Attempting Connection ...");
			connection = DriverManager.getConnection(url);
			System.out.println("Connection succeeded!");
			ResultSet RS = connection.createStatement().executeQuery("SELECT ProdID,
Descr FROM Table_Temp");
			try {

                while(RS.next() != false) {
                    System.out.println(RS.getString(1) + "  " +
RS.getString(2));
                }
            } finally {
                RS.close();
            }
		    // Success.
		} catch (SQLException e) {} finally {
		    if (connection != null) try { connection.close(); } catch
(SQLException ignore) {}
		}
	}

Hence, I modified my db-data-config.xml to

<dataConfig>
    <dataSource driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=Amazon;user=solrusr;password=solrusr;"/>
    <document>
        <entity name="Product" 
				query="SELECT ProdID,Descr FROM Table_Temp">
            <field column="ProdID" name="ProdID" />
            <field column="Descr" name="Descr" />
        </entity>
    </document>
</dataConfig>

This worked for me.

Thanks again Michael & Shawn.
O. O.










--
View this message in context: http://lucene.472066.n3.nabble.com/Solr-SQL-Express-Integrated-Security-Unable-to-execute-query-tp4035758p4036056.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: Solr SQL Express Integrated Security - Unable to execute query

Posted by Michael Della Bitta <mi...@appinions.com>.
On Thu, Jan 24, 2013 at 11:34 AM, O. Olson <ol...@yahoo.it> wrote:
>
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The server
> SQLEXPRESS is not configured to listen with TCP/IP.


That's probably your problem...


Michael Della Bitta

------------------------------------------------
Appinions
18 East 41st Street, 2nd Floor
New York, NY 10017-6271

www.appinions.com

Where Influence Isn’t a Game

Re: Solr SQL Express Integrated Security - Unable to execute query

Posted by "O. Olson" <ol...@yahoo.it>.
Shawn Heisey-4 wrote
>> There will be a lot more detail to this error.  This detail may have a 
>> clue about what happened.  Can you include the entire stacktrace?
>> 
>> Thanks,
>>Shawn

Thank you Shawn. The following is the entire stacktrace. I hope this helps:


INFO: Creating a connection for entity Product with URL:
jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;databaseName=Amazon;integratedSecurity=true;
Jan 23, 2013 3:26:05 PM org.apache.solr.core.SolrCore execute
INFO: [db] webapp=/solr path=/dataimport params={command=status} status=0
QTime=1 
Jan 23, 2013 3:26:31 PM org.apache.solr.common.SolrException log
SEVERE: Exception while processing: Product document :
SolrInputDocument[]:org.apache.solr.handler.dataimport.DataImportHandlerException:
Unable to execute query: SELECT [ProdID],[Descr] FROM
[Amazon].[dbo].[Table_Temp] Processing Document # 1
	at
org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:71)
	at
org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:252)
	at
org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:209)
	at
org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:38)
	at
org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntityProcessor.java:59)
	at
org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:73)
	at
org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:243)
	at
org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:472)
	at
org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:411)
	at
org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:326)
	at
org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:234)
	at
org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:382)
	at
org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:448)
	at
org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:429)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The server
SQLEXPRESS is not configured to listen with TCP/IP.
	at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
	at
com.microsoft.sqlserver.jdbc.SQLServerConnection.getInstancePort(SQLServerConnection.java:3188)
	at
com.microsoft.sqlserver.jdbc.SQLServerConnection.primaryPermissionCheck(SQLServerConnection.java:937)
	at
com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:800)
	at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
	at
com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:842)
	at
org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:160)
	at
org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:127)
	at
org.apache.solr.handler.dataimport.JdbcDataSource.getConnection(JdbcDataSource.java:362)
	at
org.apache.solr.handler.dataimport.JdbcDataSource.access$200(JdbcDataSource.java:38)
	at
org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:239)
	... 12 more

Jan 23, 2013 3:26:31 PM org.apache.solr.update.processor.LogUpdateProcessor
finish
INFO: [db] webapp=/solr path=/dataimport params={command=full-import}
status=0 QTime=13 {deleteByQuery=*:*} 0 13
Jan 23, 2013 3:26:31 PM org.apache.solr.common.SolrException log
SEVERE: Full Import failed:java.lang.RuntimeException:
java.lang.RuntimeException:
org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to
execute query: SELECT [ProdID],[Descr] FROM [Amazon].[dbo].[Table_Temp]
Processing Document # 1
	at
org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:273)
	at
org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:382)
	at
org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:448)
	at
org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:429)
Caused by: java.lang.RuntimeException:
org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to
execute query: SELECT [ProdID],[Descr] FROM [Amazon].[dbo].[Table_Temp]
Processing Document # 1
	at
org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:413)
	at
org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:326)
	at
org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:234)
	... 3 more
Caused by: org.apache.solr.handler.dataimport.DataImportHandlerException:
Unable to execute query: SELECT [ProdID],[Descr] FROM
[Amazon].[dbo].[Table_Temp] Processing Document # 1
	at
org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:71)
	at
org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:252)
	at
org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:209)
	at
org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:38)
	at
org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntityProcessor.java:59)
	at
org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:73)
	at
org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:243)
	at
org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:472)
	at
org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:411)
	... 5 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The server
SQLEXPRESS is not configured to listen with TCP/IP.
	at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
	at
com.microsoft.sqlserver.jdbc.SQLServerConnection.getInstancePort(SQLServerConnection.java:3188)
	at
com.microsoft.sqlserver.jdbc.SQLServerConnection.primaryPermissionCheck(SQLServerConnection.java:937)
	at
com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:800)
	at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
	at
com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:842)
	at
org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:160)
	at
org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:127)
	at
org.apache.solr.handler.dataimport.JdbcDataSource.getConnection(JdbcDataSource.java:362)
	at
org.apache.solr.handler.dataimport.JdbcDataSource.access$200(JdbcDataSource.java:38)
	at
org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:239)
	... 12 more

Jan 23, 2013 3:26:31 PM org.apache.solr.update.DirectUpdateHandler2 rollback
INFO: start rollback{flags=0,_version_=0}
Jan 23, 2013 3:26:31 PM org.apache.solr.update.DefaultSolrCoreState
newIndexWriter
INFO: Creating new IndexWriter...



--
View this message in context: http://lucene.472066.n3.nabble.com/Solr-SQL-Express-Integrated-Security-Unable-to-execute-query-tp4035758p4035992.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: Solr SQL Express Integrated Security - Unable to execute query

Posted by Shawn Heisey <so...@elyograg.org>.
On 1/23/2013 2:32 PM, O. Olson wrote:
> Hi,
>
> 	I am using the /example-DIH in the Solr 4.0 download. The example worked
> out of the box using the HSQLDB. I then attempted to modify the files to
> connect to a SQL Express instance running on my local machine. A
> http://localhost:8983/solr/db/dataimport?command=full-import results in
>
> org.apache.solr.common.SolrException log
> SEVERE: Full Import failed:java.lang.RuntimeException:
> java.lang.RuntimeException:
> org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to
> execute query: SELECT [ProdID],[Descr] FROM [Amazon].[dbo].[Table_Temp]
> Processing Document # 1
> 	at
> org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:273) …

There will be a lot more detail to this error.  This detail may have a 
clue about what happened.  Can you include the entire stacktrace?

Thanks,
Shawn