Error when trying to log in to SQL server

I've just finished developing a web app, which works fine on my PC, running IIS locally. It uses windows authentication and a trusted connection to retrieve data from SQL server. So far so routine. When I deploy the app to my live server, it returns the error:

Login failed for user '\'

The stack trace seems to indicate that it's at the point where the app tries to retrieve data that the error occurs. I proved this by changing my connection string to explicitly define the log on, and it retrieved the data no problem.

I set up a very simple webform with a label on, to return what it thought the user's log on name was, and it returns the correct name.

Any ideas as to why the username isn't being correctly passed to SQL server?Have you made sure the Web Account is a valid user of the SQL Server? If the IIS box and the SQL box are different you have to ensure the user has network rights too.|||Yes, the user (me!) definitely has rights on the SQL server. How come it works locally on my PC, but not on the server?

What network rights do you think I should investigate?

Thanks.|||Sorry I mis-read your original post, I didn't notice that your site was using NT Authentication. So here is my mental checklist:
1. Web Server has access to the Domain server to authenticate the user
2. The Web Server isn't the Domain Server
3. SQL Server security logins knows about the Windows user/user group
4. The SQL Database knows about the Windows user/user group

I mentioned the network security because when you use anon web access the user must have network access to go across machines. That "shouldn't" be an issue here.

Also run SQL Profiler to see if the user is getting set to SQL and then going wrong. Sounds more like its a problem on the web server though, something wrong with the impersonation.|||I'm positive that the user is being authenticated properly on the app, because when I load a simple page with a label which displays the user name, it displays correctly. For some reason this user name isn't getting passed to SQL server correctly.

I tried deploying the application to the box which has the SQL server running, and gess what? It worked. So does this mean that the first box can't access the domain server? Any idea how I can check that?

I'll have a think, and let you know how I get on.

Cheers.|||Hang on, just cause you're using Web Authentication doesn't mean it will impersonate the user onto SQL Server. Do you set the principal security when the user connects?

Check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch03.asp

Error when deleting

I have a msde back end for my application.
I also have an Access data file with linked tables to the SQL one.
My app accesses the data through the Access one through ODBC.

I have a data control and when try to delete a record as so:
DataControl.Recordset.Delete
I get a 'ODBC Call Failed' error.

Any ideas why?
The recordset's updateable property is true.

Thanks,THAT is the reason why I hate bound controls! Throw them away, replace dynamic calls with sp's, and live happily ever after! You think you have problems now while developing? Try putting it into prod, with more than 1 user, and you'll see what I mean (this is not an attack, just my heart screaming "WATCH OUT! THIS IS NOT A LIGHT AT THE END OF THE TUNNEL! IT IS A TRAIN!")|||Have you posted this on the Access forum for input?

Have you considered changing your Access interface to an Access Data Project? It synchronizes with SQL Server much better.

Have you checked to make sure that each of your linked tables has an enforced unique key and that Access was aware of this key when your table was linked?

Have you checked that your login has permission to delete those records?

Have you checked to be sure that the records in the table are participating in non-cascading relationships with other tables?|||...Have you checked it's plugged in?

Have you checked its pulse?

Have you checked your pulse?

...Have you checked if the neiborhood bar has started the happy hour? Go check it! It helps me!|||Here is my ms_sql_dba approved response:

OBVIOUSLY it is plugged in, or he wouldn't be getting an error message! But you didn't THINK about that, did you? And computers don't HAVE pulses, which you'd know if you bothered to read the manual!|||...?...|||...That was a joke...though I know I was accused of making my jokes somewhat untranslateable at times...But hey, that's what made me what I am, so there you have it!|||Originally posted by blindman
Here is my ms_sql_dba approved response:

OBVIOUSLY it is plugged in, or he wouldn't be getting an error message! But you didn't THINK about that, did you? And computers don't HAVE pulses, which you'd know if you bothered to read the manual!

Incorrigible

error when connect to sqlserver from another machine

hi ..

i install sqlserver and make app with vb6 using ADO to connect to the database and successfully

i put that application on another pc on the network
when i open it it says "can't login computername\guest" ?!!

any help

thanks in advance.any answers|||Are both of your pcs on a domain or a workgroup? If it's not domain, you need to create a same account/password pair on both machines and use that account to login.|||thanks

Xinwei Hong for reply

the two machines on a workgroup so .. could you explain pcs under domain in few lines ?
now i will create two users on the two machines and tell you later

thanks in advance.
|||By "pcs under domain", I mean whether your PC is under a domain which is usually used in median/large company. In this case, domain controller will help you do a lot of things.|||thanks for reply

you says i can do a lot of things if i'm under domain like what ?and what is the domain controler ? could you explain ?

and if you have short time i can talk to you in messanger it will be kind from you

thanks|||i make 2 users with same name but ..
the same problem occurs ?|||It's hard to describe "domain controller" and related stuff in short words. I recommend you find some documents related to Windows Networks to read.

Are you sure your client is running under the user account you created and you used that account to login to the server? It means you may need to login the client Windows system and run your client app under that account. If you user Administrator or even logon the computer without a user/password, it won't work.|||is this means that i need a password for administrator user to login to the database server ?|||Not necessary. SQL server runs as a service under various account(e.g. network service, local system, administrator account, etc.).

Error using Datareader Destination

Hi guys,
I'm in dire straits here - really hoping someone can help me.

I'm using a datareader destination and consuming it in a C# app. When I fire ExecuteReader on my DTSCommand object to return some data I get the error:

"Could not obtain a datareader object from the specified data flow component"

So we don't have a clue why this isn't working. Although...I'm intrigued by the line about "specified data-flow component". In the C# app you don't actually specify which data-flow component contains the data reader that you want (do you?) so how does it know where to find the datareader? I'm speculating as to whether this is something to do with the error we're getting or not but it'd help to understand anyway.

ANY help at all much appreciated. We're up against it here.

-JamieWell, the CommandText is the NAME of the DataReader Destination in the package, so your app does in fact specify the data flow component...

-Doug
|||The BOL topic on this subject, at least for the 1st Web refresh, is "Loading Data Flow Results into a Client Application." I updated it again just this week with a bit of information on using DtsDataParameter.

Unfortunately the results of attempting to copy and paste a BOL topic are never pretty...but I can try, if you'd like, or simply email you a more recent copy of the SSIS programming hxs file to drop into your BOL folder and give you the latest content (with disclaimer about editing and tech review not completed).

-Doug
|||Hi Doug,
Yeah that's the BOL topic we're using so I fear the problems may be more deep seated than that. We're definately specifying the datareader destination correctly so its not that (I guess by not specifying the data-flow the only requirement is that you don't have 2 datareader destinations with the same name in the same package).

If you could send me an updated version of that doc though that would be fantastic. I think you have my email address don't you?

Very much appreciated.

If anyone out there has some war stories of using the data-flow destination then I'm all ears.

Thanks
Jamie|||Any chance the DataReader is timing out before your app attempts to grab it?

Do you have the hex code or symbolic name (DTS_E_*) for the error that you're seeing?

-Doug
|||

DouglasL wrote:

Any chance the DataReader is timing out before your app attempts to grab it?

Do you have the hex code or symbolic name (DTS_E_*) for the error that you're seeing?

-Doug

I doubt its the timeout cos I set it to 10 seconds (I think) but I'll check it tomorrow.

The error is occurring in the C# app. To all intents and purposes the SSIS package is executing fine so I'm not going to get any DTS_E_* error(s). I'll dig around tomorrow morning though and see what I can find.

-Jamie|||As far as I can determine, the error message that you originally reported is returned ONLY when the DataReader returned to the DtsClient component by the DataReader destination (that is, the instance of System.Data.IDataReader that you expect to contain your data) is null.

At this point, DtsClient has already (1) located the component with the name that you specified in the CommandText and (2) confirmed that it is in fact a DataReader Destination component.

Hope this helps to narrow things down.

-Doug
|||Right,
The package is still failing HOWEVER I have 3 near identical packages which run fine. The calling code is exactly the same, the only difference being the connection string of my DTSConnection object and the name of the datareader.

So, there's obviously something wrong in my package (even though it runs successfully from DTExec) so I delved into the .dtsx file for each package and found the <component>...</component> section for the datareader destination. Apart from the differences in the <inputColumn> elements (cos they're different pipelines) they seem exactly the same so I can't find any problem there.

Hope this makes sense.

So, any further advice on where to search and what to search for?

-Jamie|||Jamie,

I might be shooting in the dark (I don't have access to your code and package).

Here is what I'd like to know:
1. Did that package pass if running in BI development studio? (I'm confused that in the beginning you said it fail but you also said it runs successfully from DTExec.)

2. How do you call ExecuteReader? Something similar to the following?

conn = new DtsConnection() as IDbConnection;

conn.ConnectionString = dtexeCmd; -- '-f C:\pkg.dtsx'

conn.Open();

command = conn.CreateCommand();

command.CommandText = dataReaderName;

/* Get the data reader */

reader = command.ExecuteReader();

Is it possible that you share your package and code?

|||

Runying Mao wrote:

Jamie,

I might be shooting in the dark (I don't have access to your code and package).

Here is what I'd like to know:
1. Did that package pass if running in BI development studio? (I'm confused that in the beginning you said it fail but you also said it runs successfully from DTExec.)

2. How do you call ExecuteReader? Something similar to the following?

conn = new DtsConnection() as IDbConnection;

conn.ConnectionString = dtexeCmd; -- '-f C:\pkg.dtsx'

conn.Open();

command = conn.CreateCommand();

command.CommandText = dataReaderName;

/* Get the data reader */

reader = command.ExecuteReader();

Is it possible that you share your package and code?

Hiya Runying,
Yeah, that's exactly how we do it.

The package runs successfuly in BIDS and from DTExec. The bit that fails is consuming it from elsewhere.

Can you drop me a mail (jamie.thomson[at]conchango.com) and I'll reply with the package and possibly some data (its sensitive data)

-Jamie|||

Jamie Thomson wrote:

Hiya Runying,
Yeah, that's exactly how we do it.

The package runs successfuly in BIDS and from DTExec. The bit that fails is consuming it from elsewhere.

Can you drop me a mail (jamie.thomson[at]conchango.com) and I'll reply with the package and possibly some data (its sensitive data)

-Jamie

Jamie, I've responded you with my email address.|||

Jamie,

I have found out the cause. Data Reader dest sets its property "DataReader" at validation time. But in your package, Data Flow task has "DelayValidation" set to True. This causes "DataReader" to be NULL at execution time.

It appears something we can improve. I will open a bug.

For now, the workaround is to set Data Flow task property "DelayValidation" to False.

Thanks for your package.

|||Sure, I've got the same problem in my package with a DataReader. In fact, I set the DelayValidation to true due to the query constructed at runtime and got a subject error! I'm in a hurry to try the workaround.
Thank you, Runying!

Sergei Vedischev.