console apps only work if user is SA

Hi

When I try to use a user other then SA my console apps don't work.

I can run the Stored Procedures used in console application from Query analyser when logged in with the username/password that
I'm attempting to use for the console applications.

Under Users in Enterprise Manager Database access is 'permit' for my user.

By the way my web application which uses the same user name and password as in console applications is working. I also have dts packages running using dtsexec accessing the database with the same user name and password and they work fine.

MDAC 2.8 SP2 on windows server 2003 spi

C:\Program Files\Microsoft SQL Server\80\Tools\Binn>ODBCPING.EXE -S xxx.xxx.xxx.xxx
-U myusername -P mypassword

CONNECTED TO SQL SERVER

ODBC SQL Server Driver Version: 03.86.1830

SQL Server Version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

If the connection is available then why is the adapter.fill method failing?

I tried it using a text sql statement and that doesn't work either.

The problem isn't database specific as I did a test .bat on Northwind sample database and got same 'general network error'

Here's the error:

apps\Exports>exporter.bat

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.ConnectionPool.CreateConnection()
at System.Data.SqlClient.ConnectionPool.UserCreateRequest()
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransactio
n)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, Conne
ctionState& originalState)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startR
ecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior be
havior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)

//Testing using SELECT statement as command text

D\Test>Test.bat

D:\Test>TestDBAccess.exe "server=xxx.xxx.xxx.xxx;uid=xxxx;pwd=xxx;
database=Northwind;"

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.ConnectionPool.CreateConnection()
at System.Data.SqlClient.ConnectionPool.UserCreateRequest()
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransactio
n)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()

D:\Test>

Any ideas/help much appreciated!

Hi,

would be cool if you could show us your code.Sometimes people hardcode certain properties (I confess that I did that on my own one time :-) ) which will lead to an error where usally there shouldn′t be an error (especially in console apps where connection properties are passed via arguments which isn′t testable while debugging within VS)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Here's the essentials of the code (without some try catch statements).

Thanks.

KH

.bat file

::arg 0 Connection String
::arg 1 Export Path //just a path where I want the output file to go

Exporter.exe "server=xxx.xxx.xxx.xxx;uid=username;pwd=password;database=DATABASENAME;" "d:\\xxx\\xxx\\ExportOut\\"

Source code

using System;

using System.Data;
using System.Security;
using System.Security.Permissions;
using System.Security.Policy;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;

namespace Exporter
{
class Export
{

private static String ConnectionString;

private static String ExportPath;

private static StreamWriter ExportLog;

[STAThread]
static void Main(string[] args)
{

ConnectionString = args[0];

ExportPath = args[1];

String DateString = System.DateTime.Today.Day.ToString() + "_" + System.DateTime.Today.Month.ToString();

ExportLog = new StreamWriter(ExportPath+"Export_Log_"+DateString+".txt");

DoExport();

ExportLog.Close();

}

private static void DoExport()
{

ExportLog.WriteLine("Beginning export");
GetData());

}

private static void GetData()
{

SqlCommand SelectCommand = new SqlCommand();

SelectCommand.CommandType=(System.Data.CommandType.StoredProcedure);

SelectCommand.CommandText="GetCSVOutput";

SqlConnection Conn = new SqlConnection(ConnectionString);

SelectCommand.Connection=Conn;

SqlDataAdapter ReportAdapter = new SqlDataAdapter();

FaultReportAdapter.SelectCommand=SelectCommand;

DataSet ReportData = new DataSet();

ReportAdapter.Fill(ReportData,"Report");

if (ReportData.Tables["Report"].Rows.Count == 0)
{
Conn.Close();
ExportLog.WriteLine("No reports to export");
return false;

}
else
{
Conn.Close();
return MakeFile(ReportData);

}

}

private static void MakeFile(DataSet FaultReportData)
{

/* Just prints out the results of Stored procedure to file and closes file */

}

|||Hi,

beside that the FaultReportDapater doesn′t exists (but I guess this is just a typo) you can try disabling the connection pool to see if it is based on this with adding the keywords "Pooling=False" to the connecting string.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Yes that was just a typo. With pooling set to false I still get the same error.

Thanks

KH

|||

What is the call stack of the exception if you disable pooling?

|||

Here's the call stack with pooling set to false.

D:\apps\Export>exporter.bat

D:\content\apps>Exporter.exe "server=xxx.xxx.xxx.xxx;uid=xxxx;pwd=;database=xxxx;pooling=False" "d:\\Content\\xxxx\\ExportOut\\"

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection connection
, SqlConnectionString connectionOptions)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, Conne
ctionState& originalState)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startR
ecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior be
havior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at Exporter.Export.GetData() in
\\xxxx\dotnet_dll_production
\xxxx\xxxxx\export.cs:line 536
at Exporter.Export.DoExport() in
\\xxxx\dotnet_dll_production\xxxx\xxxx\export.cs:line 143
at Exporter.Export.Main(String[] args) in \\xxxx\dotnet_dll_produc
tion\xxxx\xxxx\export.cs:line 95

D:\apps\Export>