Query To Linked Indexing Service Returns Nothing When Logged In Through SQL Authentication
Nov 7, 2006
I am struggling to get my linked indexing service to return something when I query it. The thing is, when I use Management Studio and log in through Integrated Windows authentication I have no problems. However, when logging in through a test account that only exists in SQL server I get nothing returned. Some observations:
1. Changing the test account to have sa priveliges changes nothing
2. The test account has db_owner role on source db
3. I have tried to create a login using
sp_addlinkedsrvlogin FTIndexWeb, true, 'SomeLogin' but that didn't work. I also tried to do this manually in Mgt Studio.
4. I did not find any authentication related stuff in the management console of my pc in or near the Indexing Service. I'm not sure if my system imposes any security restraints that are invisible to me.
5. My query is
SELECT * FROM OPENQUERY(DNN4_BF_DMX, 'SELECT Path, Rank FROM SCOPE() WHERE FREETEXT(Contents, ''"Confidential"'')')
6. My setup: Win XP (SP 2), SQL SRV 2005 Express, Mgt Studio Express (SP1), (if relevant) .net frameworks 1.1/2.0.
I need to tell my customers how to get this going reliably and now I feel unsure whether this is at all possible. Anyone abe to shine some light on this?
Thanks,
Peter
View 3 Replies
ADVERTISEMENT
Jul 24, 2007
Greetings. I'm wondering if y'all typically turn off filesystem indexing (IE: uncheck "Allow indexing service to index this disk for fast file searching") on the drives that hold your ldf and mdf files? Do any mssql services depend on this functionality?
Thanks,
Amy
View 1 Replies
View Related
Feb 26, 2006
Trying to limit the search using scope but I am having some problems. When I search with just FROM SCOPE(), the search works just fine. But when I try and do FROM SCOPE ('"/mydirectory"'), i get the following error. Compiler Error Message: BC30451: Name 'mydirectory' is not declared.
strSearch = "SELECT RANK, FileName, Path, VPath, Characterization, DocTitle, Size, DocAppName FROM SCOPE('"/mydirectory"') WHERE FREETEXT('" & trim(lblsearchstr.text) & "') ORDER BY RANK DESC"
View 4 Replies
View Related
Jun 6, 2007
Hi:
Am trying to access a Indexing Services catalog from SSIS. Problem is configuring the Data Connection.
I chose New Connection, Provider=Microsoft OleDB provider for Indexing Services.
Then I am not quite sure what to fill in for:
A. Server or File Name (I tried the IP address, the machine name, catalog name)
B. Location(tried catalog Name, ip address, machine name)
The Log On to Server frame is disabled.
Test Connection succeeds, but trying to use a Table or a Query does not work out.
How do I configure the Connection?
TIA
Kar
View 4 Replies
View Related
May 3, 2004
I have SQL Server 7.0 installed in a Fail-over cluster and currently, Full-text indexing is not installed, since our full-text index resides on a different physical server. In an attempt to consolidate our servers I am looking at installing full-text indexing on our cluster. I understand that there will probably be issues getting the Full-text indexing service to fail-over, but I'm not too concerned with that.
What I am concerned with, is, will I need to re-install the latest SQL Server 7.0 service pack? Does anyone know of any security holes with an un-patched version of the full-text indexing service?
Any advice on this situation is greatly appreciated. Thanks!
View 1 Replies
View Related
Jan 23, 2006
I have seen similar questions, but none of the answers have solved my problem.
I have several SQL2000 servers running under Win2003. In the past, we have been using SQL logins, but I have been trying to transition to using Windows Authentication. It has been working great except that sometimes queries using linked servers fail.
The linked servers are set up for the connection to use the login's current security context.
For testing this problem, I have limited myself to two servers to do this between. My Windows account is defined with sa rights on both servers. I have tried explicitly to impersonate my account, I have told it to explicitly use the same account and password on the other server. I have tried not listing my account and letting it default my original setting of using current security context. (if I tell it to use the sa account remotely, that does work)
Both SQL Servers run under the same domain account. That account had delegation rights. My account is not marked as sensitive. The computer account has delegation rights. There is a Service Principle Name defined for both servers.
I have tried everything I can find to try, yet I still get the following message (or something similar, depending on which settings I have changed around).
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
What am I missing?
View 41 Replies
View Related
Feb 20, 2006
Hi All
I have built this query :
Select position, SubsidiaryName
from position
Where SubsidiaryName in ('country1','country2')
and position not like('testuser_%')
and position not like ('Inactive_%')
and position not like('olduser_%');
However when I run it, the result more accounts in country 1 than expected. I think it could be inactive users who have not logged into this system for 6 months or more. How can I build a query that incorporates my original query + shows only users who logged into the past 6 months ?
View 2 Replies
View Related
Sep 24, 2015
I have set up two SQL server (e.g. Server1 and Server2) which are set up to use Windows Authentication with my login (e.g. login1).On Server1 I have created a linked server to Server2, I have then selected the Security tab and added my local login and selected impersonate. I have then selected ‘Be Made using the login’s current security context’.I then receive the error message ‘The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?’
View 9 Replies
View Related
Jun 20, 2007
Hi all!!!
I have the following problem:
I used JTDS to connect to SQL Server 2005 SP1(with Windows Authentication) from a Tomcat DataSource, using your usefull ntlmauth.dll, so I can use Users from Active Directory.
All is ok until I must call a Stored procedure that excecute queries from a linked server table.
I have the following exception message "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'"
I make a lot of test, I make a Java main program invoking the sotred procedure that excecute queries on linked server using JTDS and then using Microsoft SQL Server 2005 JDBC Driver 1.2(April 2007)sqljdbc.jar and Ihave the same problem, but if we use the bridge JDBC-ODBC all works OK.
Please help!!
thanks
Lore
View 4 Replies
View Related
Sep 9, 2015
I have two SQL Server machines - RPTPROD and DATAPROD.
I wanted to create a linked server from RPTPROD pointing to DATAPROD using Windows Authentication. Â When I tried to create this linked server, I keep getting this error
"Login failed for user 'NT AUTHORITYANNOYMOUS LOGON' (Microsoft SQL Server,Error: 18456)". Â
On the "Security" page, I chose "Be made using the login's current security context". Â I'm sysadmin on both SQL Server machines. Â
View 3 Replies
View Related
Apr 10, 2008
Hello,
I am trying to deploy reporting service (SQL Server 2005) report on server from visual studio.
When i click on deploy menu.
System asks for user name and password.
Exactly as below
Server : http://machinename/reports
User name :
Password :
I am providing windows user identity (same as web service identity in reporting service configuration wizard). Authentication fails.
Kindly suggest ways to solve it. I may be making mistakes as well.
Thanks & Regards,
Kalai
View 1 Replies
View Related
Mar 26, 2008
Does Report manager in Reporting Services 2005 support form Authentication?If so where can i go to enable it?
Thank you
Kenalex
View 1 Replies
View Related
Jan 17, 2006
I've tried creating several Web Service Tasks and had no problems until I came across one that requires authentication. I have a standalone program to handle this Web Service, but I'm experimenting with SSIS to see if I can replace it.
Anyone know how to handle this?
Thanks
-- Scott
View 2 Replies
View Related
May 15, 2007
I have a linked server defined on a SQL Server 2005 SP2 standard edition server using the IBMDASQL OLEDB driver. The linked server has been defined and working for months when used from a SQL Server authenticated session. I started converting our developers to Windows Authentication and access to the linked server is denied when used from a Windows Authenticated session. Here are the error messages:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "IBMDASQL" for linked server "DB2ARUBA" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "IBMDASQL" for linked server "DB2ARUBA".
The linked server security is defined to login to the IBM iSeries using a fixed user and password regardless of the login using the linked server.
In order to analyze the problem, I created one sysadmin SQL Server authenticated login and one sysadmin Windows Authenticated login. The Windows Authenticated login refers to a domain login having administrator priviledges on the local SQL Server box.
So, when I connect to SQL Server using the SQL Authenticated login, a query against the linked server works. When I connect to SQL Server using the Windows Authenticated login, the exact same query against the linked server fails with the messages above.
Does anyone know why?
View 10 Replies
View Related
Apr 29, 2008
I am trying to upgrade my default instance from SQL Server 2000 Standard to SQL Server 2005 Standard. I get to the service authentication window and hit my first problem. On that window, there is a dropdown box where you should be able to choose the default instance service AND the SQL Server Agent service and set the authentication information for both of those services. That's the way it is in my test environment. However, on my production server, when I click that dropdown box, only the instance appears, and not the agent. Why?
I tried the upgrade this past weekend and ran into this problem. After researching it and not finding anything out about it online, I decided to just go forward anyway since I had lots of backups and copies of everything. The upgrade appeared to go ok, but the agent service wouldn't start. It gave the usual message when you try to start a service that doesn't have anything to do, like the Performance Logs and Alerts service. When I looked at the agent in SQL Server Management Studio, it had (Agent XPs Disabled) next to it. So I researched this and found how to enable it. For some people online, this allowed them to start the agent service, but of course that didn't work for me. Finally in my research online I found that some individuals had a lot of missing system tables in their MSDB and Master databases after the upgrade due to some of the SQL scripts that are part of the upgrade process not running properly. So I compared my system tables and SP's against another SQL 2005 instance I have, and sure enough, there appears to be a lot of missing tables. It was at that point I decided to just restore everything and try again later.
My point in writing that second paragraph is to ask if that was all due to the fact that the SQL Server Agent wasn't listed in the upgrade wizard like it should have been? Or maybe the fact that it wasn't in that dropdown box was evidence of a deeper problem? Is it likely that if I do whatever it is I need to do to make the agent appear in the upgrade wizard that my missing system tables and agent service not starting problems would probably not be an issue anymore? And why would the agent not be appearing in the upgrade wizard?
Thanks for your help!
View 2 Replies
View Related
Jun 5, 2007
Maybe someone can help me. I am running the reporting service add in for Sharepoint and we have only Basic Authentication running on our site (it is on SSL). When i try and open a report in sharepoint it errors out on me. When i go back into IIS and set Windows and Basic authentication it works, but it requires me to enter our domain and username. We need to only use Basic so it will not need the domain. Anyone have any suggestions on what i can do to make this work?
View 5 Replies
View Related
Aug 2, 2011
Is it possible to configure a datasource using data from a web service (java) requiring basic http authentication?
View 4 Replies
View Related
Dec 16, 2013
I am attempting to set up an always on cluster on VMware for testing. setting up everything through the Failover cluster is fine, the trouble comes when I try to set up the AlwaysOn availability group. Whenever I attempt to specify a network location I receive the following error: Operating System Error 1265(The system cannot contact a domain controller to service the authentication request..).
I looked up this error and most sites point towards a Windows 8 homegroup issue. Since this is a on a domain, I don't think it is relevant. I also added the service accounts from server A to Server B and visa versa. I even added the computer objects to its opposite partner..I have attempted to use "Join Only" and do a manual copy.
View 1 Replies
View Related
Sep 22, 2006
Hi
I want to know is a flat file faster than a RDBMS for indexing for example a search engine indexing would a flat file be better in terms of performance, scalability etc than a RDBMS?
View 14 Replies
View Related
Feb 23, 1999
When I was using a simple query using select statement with where clauses, I can get the results. When
I use AND to specify more conditions. It returns no row even though I get the result when query seperately.
What should be the possible cause of this. I am using SQL Server 6.5. Thank you
View 2 Replies
View Related
Dec 10, 2012
I have written sql query
select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
INVOICETYPEMASTER.InvoiceTypeName, INVOICEITEMS.ItemQuantity as SumQuantity,
INVOICE.BasicValue ,INVOICE.BasicValue * INVOICE.ExchangeRate +
[Code].....
I am getting different amount 984000.0000 and quantity 9.
View 1 Replies
View Related
Sep 10, 2007
Hi,
The other day we tried online re-indexing feature of SQL 2005 and it€™s performing faster than offline re-indexing. Could you please validate if it€™s supposed to do be this way? I always thought offline should be faster than online.
Thanks,
Ritesh
View 5 Replies
View Related
May 5, 2008
Hi Guys:
Our company wanted to try out SQL Server 2005 Enterprise Edition (64 Bit). So, we were on free trial of the Enterprise Edition for past 5 months. After which we decided to go for SQL SERVER 2005 Standard Edition (64 Bit). And, Last week, we installed the SQL Server Standard Edition (64 Bit) on our server. After installation, everything was restored as before.
The version we are on right now is:
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Strangely, a job which ran fine till then is failing with the following error: And, to be more specific, when this job is run manually in the form of a stored procedure using the query optimizer it runs fine. But, when its executed as a scheduled job on SQL Agent, it fails. The History logs record the following error.
Error :
The OLE DB provider "SQLNCLI" for linked server "LV-SQL2" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "LV-SQL2". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI" for linked server "LV-SQL2" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412).
I tried everything possible, even recreated the job, but, no avail.
I also considered the possibility if SQL Agent login account did not have enough permissions. So, I changed the SQL Agent login to windows authentication, but the job still fails. So, its the problem has nothing to do with login accounts either.
For the record, I have cross checked all these too.
1. Enabled the remote connections in Surface Area Config
2. Added local server login to remote server login mappings.
3. Checked 'Rpc' and 'Rpc Out' under server options. Also, the Connection Timeout and Query Timeout have been set to zero (0).
Any suggestions pointing towards problem solution appreciated.
Thank you.
View 4 Replies
View Related
Jul 14, 2006
I have configured a non-SQL linked server (via an OLE DB provider) and I wish to insert data into it via Service Broker but I am getting the following error in the SQL Server log:
The activated proc [dbo].[sp_ mytableServiceProgram] running on queue TestDB.dbo.mytableQueue output the following: 'Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.'
As you see below, my strored proc. is not issuing any 'save trans' statements, so why is it not allowing me to wrap my code in a transaction? How else can I use a transaction (in order to not lose anything from the queue) and yet still be able to insert to the linked server?
CREATE PROC sp_mytableServiceProgram
AS
SET NOCOUNT ON;
DECLARE
@XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName nvarchar(256),
@Dialog UNIQUEIDENTIFIER;
-- This procedure continues to process messages in the queue until the
-- queue is empty.
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION;
--BEGIN DISTRIBUTED TRANSACTION; --Tried this but didn't help.
-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@Dialog = conversation_handle
FROM mytableQueue
), TIMEOUT 2000 ;
-- If RECEIVE did not return a message, roll back the transaction
-- and break out of the while loop, exiting the procedure.
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END ;
SET @XML = CAST(@MessageBody AS XML);
INSERT INTO LINKEDSERVER.dbname.user.mytable
SELECT tbl.rows.value('@doc_no', 'INT') AS doc_no,
tbl.rows.value('@queryid', 'NVARCHAR(50)') AS queryid,
tbl.rows.value('@ar_num', 'NVARCHAR(50)') AS ar_num,
tbl.rows.value('@status', 'NVARCHAR(20)') AS status,
tbl.rows.value('@creationtime', 'DATETIME') AS creationtime,
tbl.rows.value('@note', 'NVARCHAR(250)') AS note,
tbl.rows.value('@posted', 'NCHAR(1)') AS posted,
tbl.rows.value('@kms', 'INT') AS kms,
tbl.rows.value('@schresid', 'NVARCHAR(50)') AS schresid,
tbl.rows.value('@resolution_code', 'NCHAR(8)') AS resolution_code,
tbl.rows.value('@page_count', 'INT') AS page_count,
tbl.rows.value('@new_serial_number', 'NVARCHAR(20)') AS new_serial_number,
tbl.rows.value('@taskresolution', 'NVARCHAR(250)') AS taskresolution
FROM @XML.nodes('/inserted') tbl(rows);
-- If the INSERT did not insert any rows, rollback.
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
COMMIT TRANSACTION;
END
GO
View 8 Replies
View Related
Nov 29, 2012
I am having a query
select INVOICE.TarrifHeadNumber, SUM(INVOICEITEMS.ItemQuantity) From invoiceitems,
invoice Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012'
group by INVOICE.TarrifHeadNumber
tarrifheadno SUM(INVOICEITEMS.ItemQuantity)
84195030 9.00
84198910 5.00
84212190 223.00
84569090 247.00
84799040 1138.00
8481-80-1030 137.00
85433000 6177.20
tarrifheadno is unique
Now if i use below query and add invoicetypecode field
select INVOICE.TarrifHeadNumber,CETSH.GoodsDescription, SUM(INVOICEITEMS.ItemQuantity),INVOICE.invoicetype code From invoiceitems,
invoice , cetsh Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012' and
cast(CETSH.CETSHNumber as varchar) = INVOICE.TarrifHeadNumber group by INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,in voicetypecode
This query return distinct of 11 records
84195030 84195030
9.00 1
84198910 84198910
5.00 2
84212190 84212190
157.00 1
84212190 84212190
42.00 2
84212190 84212190
24.00 3
84569090 84569090
189.00 1
84569090 84569090
58.00 2
84799040 84799040
166.00 1
84799040 84799040
972.00 2
85433000 85433000
3764.00 1
85433000 85433000
2413.20 2
How to get same 7 records
View 1 Replies
View Related
Dec 13, 2014
Why does this return 64.00000 Hours Worked when if you do the calculation yourself you see that it should only be 16?
Code:
Create Table #One
(
BadgeNum int,
NameOnFile varchar(1000),
hoursworked int
[code]....
View 3 Replies
View Related
Sep 3, 2014
I have a SP that manipulates data for picking products and puts them into a temp table "#PickList" which is used for the basis of printing a picking note report.
I have also added code at the end of the SP to take the "#PickList" data and insert into a permanent table called "BWT_Lift_Transaction" and then use the bcp command to query it out to a text file. All this works fine, until the bcp command runs. Although the records are in the table, bcp returns nothing. Here is the code:
DECLARE @strLocation VARCHAR(50)
DECLARE @TransNum VARCHAR(50)
DECLARE @strFileLocation VARCHAR(1000)
DECLARE @strFileName VARCHAR(1000)
DECLARE @bcpCommand VARCHAR(8000)
[code].....
The earlier parts of the code create the filename for the text file and location to store it. If I insert a SELECT on the dbo.BWT_Lift_Transaction directly after the insert, I can see the data in there, but although the bcp command creates the file correctly, it returns no data:
output
NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL
If I remove the delete statement at the end and run the code twice, it will insert the data into the table twice. On the first run, nothing is returned by bcp. On the second run, the first set is returned by bcp, not both sets.
I don't understand why this is, but I guess it's something to do with transaction commitment and the way bcp works.
View 1 Replies
View Related
Dec 16, 2007
I want a query that returns only the column names
View 4 Replies
View Related
Sep 21, 2006
Hello,
I'm trying to update column based upon the results of a subquery. I'm getting the error that my Subquery returns more than one result. I've tried adding the EXISTS or IN keywords and cannot get the syntax right. I can't find any examples of how to write this with an Update query.
Here's my query:
UPDATE temp_UpdateRemainingHours
SET UsedHours =
(SELECT dbo.vw_SumEnteredHours_byCHARGE_CD.SumEnteredHours, dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD
FROM dbo.vw_SumEnteredHours_byCHARGE_CD INNER JOIN
dbo.temp_UpdateRemainingHours ON
dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD)
FROM dbo.vw_SumEnteredHours_byCHARGE_CD, temp_UpdateRemainingHours
WHERE
dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD
;
View 5 Replies
View Related
Jul 13, 2015
I am facing a problem that following query give me different result whenever I execute the query, it returns me same number of records but not all the rows are same everytime it is executed.
Select[Field1]
From
(
SelectRow_number() Over(Partition By [Field2], [Field3] Order By [Field2], [Field3], [Field4] Desc) ID, *
From[dbo].[Table1]
) A
WhereID > 1
OrderBy [Field1]
Those highlighted in yellow colours are duplicate records, I need to remove one of them.
View 8 Replies
View Related
Aug 6, 2015
This is my syntax, and if I print the value that is stored in each variable @goodtries = 120 @badtries = 25 but when I run the syntax below it gives me 0.00
Declare @goodtries as int, @badtries as int
select @goodtries = convert(decimal(18,4),count(userID))
from table1
WHERE logintype IN ('Valid', 'Success')
select @badtries = convert(decimal(18,4),count(userID))
[code].....
View 7 Replies
View Related
Jul 24, 2007
Hi,I have an Address table which contains more than one addresses for aparticular member. I want to write a query that would only displaymost current address. All addresses have a unique ID (addID).Example:memberID addID address1-------- ------ --------------------------------------------------295 69 13 Auster St295 70 465 Lorre Ct295 71 P.O. Box 321722 171 10 Hannaford Rd722 172 Dubai, United Arab Emirates
Quote:
View 1 Replies
View Related
Jul 28, 2006
I am hoping someone could help me understand why this is happening and perhaps a solution.
I am using ASP.NET 2.0 with a SQL 2005 database.
In code behind, I am performing a query using a parameter as below:
sql = "SELECT field_name FROM myTable WHERE (field_name = @P1)"
objCommand.Parameters.Add(New SqlParameter("@P1", TextBox1.Text))
The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.
The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:
sql = String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)
I also get NO records retuned using the same criteria.
What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?
Thanks!
View 7 Replies
View Related
