failed to connect to SQL server from asp.net

Hello,
I have an asp.net application which connects to SQL server using the
normal ado.net mechanism. One customer cannot get it to connect to the
database. his connection string is
user id=aaaa;password=bbbb;initial catalog=cccc;server=dddd
and he gets the message:
Cannot open database requested in login 'cccc'. Login fails. Login
failed for user 'aaaa'.
The customer insists that he has the values in the connection string
correct; the only unusual thing is that dddd is actually the IP address
rather than the host name of the SQL server box.
I am not in a position to do any direct investigation myself. many
other customers are using the same software without problems. does
anyone have any clues what might cause this error, other than invalid
username/password combination?
TIA
Andyajfish@.blueyonder.co.uk wrote:
> Hello,
> I have an asp.net application which connects to SQL server using the
> normal ado.net mechanism. One customer cannot get it to connect to the
> database. his connection string is
> user id=aaaa;password=bbbb;initial catalog=cccc;server=dddd
> and he gets the message:
> Cannot open database requested in login 'cccc'. Login fails. Login
> failed for user 'aaaa'.
> The customer insists that he has the values in the connection string
> correct; the only unusual thing is that dddd is actually the IP
> address rather than the host name of the SQL server box.
> I am not in a position to do any direct investigation myself. many
> other customers are using the same software without problems. does
> anyone have any clues what might cause this error, other than invalid
> username/password combination?
>
That's the only cause I know of.
if the IP address was the problem, he would be getting a "not found"
message rather than "login failed", which implies a connection to the
database was initiated, but terminated due to improper credentials.
The customer should check the sql error logs as well as the event logs
to see if any other errors or warnings are present.
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||If the Sql Server is not enabled for "Mixed mode" (both SQL and Windows)
authentication, they could possibly get that error.
Peter
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com
"ajfish@.blueyonder.co.uk" wrote:

> Hello,
> I have an asp.net application which connects to SQL server using the
> normal ado.net mechanism. One customer cannot get it to connect to the
> database. his connection string is
> user id=aaaa;password=bbbb;initial catalog=cccc;server=dddd
> and he gets the message:
> Cannot open database requested in login 'cccc'. Login fails. Login
> failed for user 'aaaa'.
> The customer insists that he has the values in the connection string
> correct; the only unusual thing is that dddd is actually the IP address
> rather than the host name of the SQL server box.
> I am not in a position to do any direct investigation myself. many
> other customers are using the same software without problems. does
> anyone have any clues what might cause this error, other than invalid
> username/password combination?
> TIA
> Andy
>|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:enZRMTliGHA.4776@.TK2MSFTNGP05.phx.gbl...
> ajfish@.blueyonder.co.uk wrote:
> That's the only cause I know of.
when I looked at the error message a little closer and reproduced it, I
realised the problem was actually down to the database name being incorrect,
not the username and password
d'oh !

> if the IP address was the problem, he would be getting a "not found"
> message rather than "login failed", which implies a connection to the
> database was initiated, but terminated due to improper credentials.
> The customer should check the sql error logs as well as the event logs
> to see if any other errors or warnings are present.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:enZRMTliGHA.4776@.TK2MSFTNGP05.phx.gbl...
> ajfish@.blueyonder.co.uk wrote:
> That's the only cause I know of.
when I looked at the error message a little closer and reproduced it, I
realised the problem was actually down to the database name being incorrect,
not the username and password
d'oh !

> if the IP address was the problem, he would be getting a "not found"
> message rather than "login failed", which implies a connection to the
> database was initiated, but terminated due to improper credentials.
> The customer should check the sql error logs as well as the event logs
> to see if any other errors or warnings are present.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>

fabricated hierarchical recordset

I want to use fabricated hierarchical recordset in VB6 using ADO. I wrote
code like
dim rs as adodb.recordest
set rs=new adodb.recordest
rs.fields.append "a1",adChar,30
Then in loop I put
rs.addnew
rs("a1")=...
re.update
when I associated this with hierarchical flexgrid I saw what I expected. On
the next step I added line
rs.fields.append "a2",adChapter
and this operator gave me error that I use wrong parms. Then I realized that
I should use specific connection. But with this connection adChar stopped to
work also. Is it possible to resolve this problem?
--
Aleks Kleyn
http://www.geocities.com/aleks_kleynHi Alex,

check next KB about how to create hierarchical recordset programmatically

http://support.microsoft.com/defaul...kb;en-us;196029

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp

"ALEX KLEIN" <alekskleyn@.optonline.net> wrote in message
news:Vip2b.193279$_R5.71553770@.news4.srv.hcvlny.cv .net...
> I want to use fabricated hierarchical recordset in VB6 using ADO. I wrote
> code like
> dim rs as adodb.recordest
> set rs=new adodb.recordest
> rs.fields.append "a1",adChar,30
> Then in loop I put
> rs.addnew
> rs("a1")=...
> re.update
> when I associated this with hierarchical flexgrid I saw what I expected.
On
> the next step I added line
> rs.fields.append "a2",adChapter
> and this operator gave me error that I use wrong parms. Then I realized
that
> I should use specific connection. But with this connection adChar stopped
to
> work also. Is it possible to resolve this problem?
> --
> Aleks Kleyn
> http://www.geocities.com/aleks_kleyn

Extreme performance issues (SQL Server 2000/ADO.NET/C#)

I posted this on the ADO.NET group, and someone there suggested I post
it here too. I appreciate any tips.
I'm using ADO.NET in a windows service application to perform a process on
SQL Server 2000. This process runs very quickly if run through Query
Analyser or Enterprise Manager, but takes an excessively long time when run
through my application. To be more precise, executing stored procedures and
views through Query Analyser take between 10 and 20 seconds to complete. The
same exact stored procedures and views, run in the same exact order, through
my program, take anywhere from 30 minutes to 2 hours to complete, and the
system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical
ram) is pegged at 25% cpu usage (the query uses 100% of a single cpu's worth
of processing power). I am at a complete loss as to why such a vast
difference in execution time would occurr, but here are some details.
The windows service executes on a workstation.
SQL Server 2000 executes on a server different from the workstation through
a 100mbps ethernet network.
Query Analyser/Enterprise Manager run on the same workstation as the windows
service.
The process is as follows:
1) Run a stored procedure to clear temp tables.
2) Import raw text data into a SQL Server table (Reconciliation).
3) Import data from a Microsoft Access database into 3 SQL Server tables
(Accounts, HistoricalPayments, CurrentPayments).
(This takes about 10 - 15minutes to import 70,000 - 100,000 records from
an access database, housed on a network share on a different server.)
4) "Bucketize" the imported data. This process gathers data from the 4
tables stated so far (Reconciliation, Accounts, HistoricalPayments,
CurrentPayments, and places records into another table (Buckets) and
assigned a primary category number to each record through a stored
procedure.
5) Sort buckets of data into subcategories, updating each record in
(Buckets) and assigning a sub category number, through another stored
procedure.
6) Retrieve a summary of the data in (Buckets) (this summary is a count of
rows and summation of monetary values), grouped by the primary category
number. This is a view.
7) Retrieve a summary of the data in (Buckets), grouped by both the primary
and sub category numbers. This is a view.
When I execute these steps manually through query analyser, (save step 3),
each query takes anywhere from 1 second to 20 seconds. The views,
surprisingly, take more time than the fairly complex stored procedures of
step 4 and 5.
When I execute these steps automatically using my windows service (written
in .NET, C#, using ADO.NET), the simple stored procedures like clearing
tables and whatnot execute quickly, but the stored procedures and views from
steps 4-7 take an extremely long time. The stored procedures take at a
minimum 30 minutes to complete, and sometimes nearly an hour. The views are
the worst of all, taking no less than 1 hour to run, and often two hours
(probably longer, actually, since my CommandTimeout is set to 7200 seconds,
or two hours). I have never seen such a drastic difference between the
execution of a query or stored procedure between query analyser and an
application. There should be little or no difference at all, considering
that everything is stored procedures (even the views...I wrap all the views
in a simple stored procedure that calls the view using a SELECT), and as
such executes on the server. Not only that, but Query Analyser is running on
the same exact box that the application is running on, and is connecting ot
the same SQL Server.
I doubt this is a network bandwidth issue, as after calling the stored
procedure from code, there is no network activity except mssql keep-alive
messages, until the procedure completes and returns its result set or return
value (if any), and then its only a momentary blip as the data is sent
accross.
I've followed proper practice when using views and stored procedures. When I
select, I always explicitly name the columns I wish to retrieve. I have
appropriate indexes on the columns in the 4 data tables. The queries that
execute in the stored procedures are fairly complex, involving summations,
count(), group by, and order by. I can understand a moderate difference in
performance between query analyser and an ADO.NET application due to
ADO.NETs extra overhead, but a difference between 20 seconds and 1 hour is
more than can be attributed to .NET overhead.
I greatly appreciate anyone who might have some insight to this offering
some help. I've scanned the net looking for similar situations, but
searching for them is somewhat difficult, considering the nature and volume
of factors. Thanks.
-- Jon Rista
Have you profiled both SQL Server and your application to find out where all
of the time is coming from? You should run SQL Server Profiler to find out
if the stored procedures are really taking 30 minutes (and what parameters
are really getting passed in -- it could be that something is not getting
properly passed in). If it turns out that the stored procedures are not
taking up the time, you should download a copy of Compuware's DevPartner
Profiler, Community Edition ( www.compuware.com ). Then you can profile
your app to find out what's going on.
When you say you're "using ADO.NET", what does that mean? Are you using a
data reader? Data adaptor? What are you doing with the data once it's
loaded into the reader/data set?
"Jon Rista" <jrista@.hotmail.com> wrote in message
news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
> I posted this on the ADO.NET group, and someone there suggested I post
> it here too. I appreciate any tips.
> I'm using ADO.NET in a windows service application to perform a process on
> SQL Server 2000. This process runs very quickly if run through Query
>
|||When you profile your server, if you find that there is a
performance issue with a stored proc, you might want to
include the "Stored Procedure- SP:StmtCompleted" event in
order to see if there is a particular statement within
your stored procs that is takign all the time.
Also, if it isn't already there, add "SET NOCOUNT ON" at
the top of your stored procedures.
We have seen some preformance issue from .NET Web
Services but have not used .NET Windows Services. I have
a feeling that this may actually be your problem.
I hope that this helps somehow.
Matthew Bando
Matthew.Bando@.Remove csctgi.com

>--Original Message--
>Have you profiled both SQL Server and your application
to find out where all
>of the time is coming from? You should run SQL Server
Profiler to find out
>if the stored procedures are really taking 30 minutes
(and what parameters
>are really getting passed in -- it could be that
something is not getting
>properly passed in). If it turns out that the stored
procedures are not
>taking up the time, you should download a copy of
Compuware's DevPartner
>Profiler, Community Edition ( www.compuware.com ). Then
you can profile
>your app to find out what's going on.
>When you say you're "using ADO.NET", what does that
mean? Are you using a
>data reader? Data adaptor? What are you doing with the
data once it's[vbcol=seagreen]
>loaded into the reader/data set?
>
>"Jon Rista" <jrista@.hotmail.com> wrote in message
>news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
suggested I post[vbcol=seagreen]
perform a process on[vbcol=seagreen]
through Query
>
>.
>

Extreme performance issues (SQL Server 2000/ADO.NET/C#)

I posted this on the ADO.NET group, and someone there suggested I post
it here too. I appreciate any tips.
I'm using ADO.NET in a windows service application to perform a process on
SQL Server 2000. This process runs very quickly if run through Query
Analyser or Enterprise Manager, but takes an excessively long time when run
through my application. To be more precise, executing stored procedures and
views through Query Analyser take between 10 and 20 seconds to complete. The
same exact stored procedures and views, run in the same exact order, through
my program, take anywhere from 30 minutes to 2 hours to complete, and the
system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical
ram) is pegged at 25% cpu usage (the query uses 100% of a single cpu's worth
of processing power). I am at a complete loss as to why such a vast
difference in execution time would occurr, but here are some details.
The windows service executes on a workstation.
SQL Server 2000 executes on a server different from the workstation through
a 100mbps ethernet network.
Query Analyser/Enterprise Manager run on the same workstation as the windows
service.
The process is as follows:
1) Run a stored procedure to clear temp tables.
2) Import raw text data into a SQL Server table (Reconciliation).
3) Import data from a Microsoft Access database into 3 SQL Server tables
(Accounts, HistoricalPayments, CurrentPayments).
(This takes about 10 - 15minutes to import 70,000 - 100,000 records from
an access database, housed on a network share on a different server.)
4) "Bucketize" the imported data. This process gathers data from the 4
tables stated so far (Reconciliation, Accounts, HistoricalPayments,
CurrentPayments, and places records into another table (Buckets) and
assigned a primary category number to each record through a stored
procedure.
5) Sort buckets of data into subcategories, updating each record in
(Buckets) and assigning a sub category number, through another stored
procedure.
6) Retrieve a summary of the data in (Buckets) (this summary is a count of
rows and summation of monetary values), grouped by the primary category
number. This is a view.
7) Retrieve a summary of the data in (Buckets), grouped by both the primary
and sub category numbers. This is a view.
When I execute these steps manually through query analyser, (save step 3),
each query takes anywhere from 1 second to 20 seconds. The views,
surprisingly, take more time than the fairly complex stored procedures of
step 4 and 5.
When I execute these steps automatically using my windows service (written
in .NET, C#, using ADO.NET), the simple stored procedures like clearing
tables and whatnot execute quickly, but the stored procedures and views from
steps 4-7 take an extremely long time. The stored procedures take at a
minimum 30 minutes to complete, and sometimes nearly an hour. The views are
the worst of all, taking no less than 1 hour to run, and often two hours
(probably longer, actually, since my CommandTimeout is set to 7200 seconds,
or two hours). I have never seen such a drastic difference between the
execution of a query or stored procedure between query analyser and an
application. There should be little or no difference at all, considering
that everything is stored procedures (even the views...I wrap all the views
in a simple stored procedure that calls the view using a SELECT), and as
such executes on the server. Not only that, but Query Analyser is running on
the same exact box that the application is running on, and is connecting ot
the same SQL Server.
I doubt this is a network bandwidth issue, as after calling the stored
procedure from code, there is no network activity except mssql keep-alive
messages, until the procedure completes and returns its result set or return
value (if any), and then its only a momentary blip as the data is sent
accross.
I've followed proper practice when using views and stored procedures. When I
select, I always explicitly name the columns I wish to retrieve. I have
appropriate indexes on the columns in the 4 data tables. The queries that
execute in the stored procedures are fairly complex, involving summations,
count(), group by, and order by. I can understand a moderate difference in
performance between query analyser and an ADO.NET application due to
ADO.NETs extra overhead, but a difference between 20 seconds and 1 hour is
more than can be attributed to .NET overhead.
I greatly appreciate anyone who might have some insight to this offering
some help. I've scanned the net looking for similar situations, but
searching for them is somewhat difficult, considering the nature and volume
of factors. Thanks.
-- Jon RistaHave you profiled both SQL Server and your application to find out where all
of the time is coming from? You should run SQL Server Profiler to find out
if the stored procedures are really taking 30 minutes (and what parameters
are really getting passed in -- it could be that something is not getting
properly passed in). If it turns out that the stored procedures are not
taking up the time, you should download a copy of Compuware's DevPartner
Profiler, Community Edition ( www.compuware.com ). Then you can profile
your app to find out what's going on.
When you say you're "using ADO.NET", what does that mean? Are you using a
data reader? Data adaptor? What are you doing with the data once it's
loaded into the reader/data set?
"Jon Rista" <jrista@.hotmail.com> wrote in message
news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
> I posted this on the ADO.NET group, and someone there suggested I post
> it here too. I appreciate any tips.
> I'm using ADO.NET in a windows service application to perform a process on
> SQL Server 2000. This process runs very quickly if run through Query
>|||When you profile your server, if you find that there is a
performance issue with a stored proc, you might want to
include the "Stored Procedure- SP:StmtCompleted" event in
order to see if there is a particular statement within
your stored procs that is takign all the time.
Also, if it isn't already there, add "SET NOCOUNT ON" at
the top of your stored procedures.
We have seen some preformance issue from .NET Web
Services but have not used .NET Windows Services. I have
a feeling that this may actually be your problem.
I hope that this helps somehow.
Matthew Bando
Matthew.Bando@.Remove csctgi.com
>--Original Message--
>Have you profiled both SQL Server and your application
to find out where all
>of the time is coming from? You should run SQL Server
Profiler to find out
>if the stored procedures are really taking 30 minutes
(and what parameters
>are really getting passed in -- it could be that
something is not getting
>properly passed in). If it turns out that the stored
procedures are not
>taking up the time, you should download a copy of
Compuware's DevPartner
>Profiler, Community Edition ( www.compuware.com ). Then
you can profile
>your app to find out what's going on.
>When you say you're "using ADO.NET", what does that
mean? Are you using a
>data reader? Data adaptor? What are you doing with the
data once it's
>loaded into the reader/data set?
>
>"Jon Rista" <jrista@.hotmail.com> wrote in message
>news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
>> I posted this on the ADO.NET group, and someone there
suggested I post
>> it here too. I appreciate any tips.
>> I'm using ADO.NET in a windows service application to
perform a process on
>> SQL Server 2000. This process runs very quickly if run
through Query
>
>.
>

Extranet Thick Client Connection

Hi,
I have a Windows Forms application that uses ADO.NET 1.1 to communicate with SQL Server 2K. I would like to start retailing my Windows app, allowing clients who are not in my domain or work-group to communicate with a database on MY SQL Server using Wind
ows Integrated Authentication. Can I get some guidance on exactly how this would be done please?
JT
Windows Authentication without a domain means you're using Workgroup
Security. This essentially requires you to duplicate nt user accounts and
passwords on both machines. This will work with Named Pipe connections.
It will not work with Sockets.
I'd recommend securing the traffic with SQL's protocol encryption. You'll
need a server certificate installed on the SQL Server.
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Thanks Kevin - I appreciate it. How do you communicate with Named Pipes over the internet? My clients would be at sites far remote from my SQL Server(s). Would I have to have a VPN? Would exposing SQL Server as a Web Service be an option?
JT
"Kevin McDonnell [MSFT]" wrote:

> Windows Authentication without a domain means you're using Workgroup
> Security. This essentially requires you to duplicate nt user accounts and
> passwords on both machines. This will work with Named Pipe connections.
> It will not work with Sockets.
> I'd recommend securing the traffic with SQL's protocol encryption. You'll
> need a server certificate installed on the SQL Server.
> 276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
> Server
> http://support.microsoft.com/?id=276553
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||You would not use Named Pipes over the internet. The ports required for it
to work would make your
firewall insecure.
If you're remote clients are VPNing in, then I would think that they'd also
would be authenticating
against a DC. So, after they've done this they should be able to use
Windows Auth to SQL.
If you're clients are coming from an Extranet, do you have a 1 way Trust
between machines there and your other domain?
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Extranet Thick Client Connection

Hi,
I have a Windows Forms application that uses ADO.NET 1.1 to communicate with
SQL Server 2K. I would like to start retailing my Windows app, allowing cl
ients who are not in my domain or work-group to communicate with a database
on MY SQL Server using Wind
ows Integrated Authentication. Can I get some guidance on exactly how this
would be done please?
JTWindows Authentication without a domain means you're using Workgroup
Security. This essentially requires you to duplicate nt user accounts and
passwords on both machines. This will work with Named Pipe connections.
It will not work with Sockets.
I'd recommend securing the traffic with SQL's protocol encryption. You'll
need a server certificate installed on the SQL Server.
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks Kevin - I appreciate it. How do you communicate with Named Pipes ove
r the internet? My clients would be at sites far remote from my SQL Server(
s). Would I have to have a VPN? Would exposing SQL Server as a Web Service
be an option?
JT
"Kevin McDonnell [MSFT]" wrote:

> Windows Authentication without a domain means you're using Workgroup
> Security. This essentially requires you to duplicate nt user accounts and
> passwords on both machines. This will work with Named Pipe connections.
> It will not work with Sockets.
> I'd recommend securing the traffic with SQL's protocol encryption. You'll
> need a server certificate installed on the SQL Server.
> 276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
> Server
> http://support.microsoft.com/?id=276553
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||You would not use Named Pipes over the internet. The ports required for it
to work would make your
firewall insecure.
If you're remote clients are VPNing in, then I would think that they'd also
would be authenticating
against a DC. So, after they've done this they should be able to use
Windows Auth to SQL.
If you're clients are coming from an Extranet, do you have a 1 way Trust
between machines there and your other domain?
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.