Console apps work for SA but no other user

Hi

My console applications work forSA and no other user. I can run the Stored procedures used in the console application from Query analyser when logged in with username/password that I am attempting to use for console applications. I am using SQL server authenication. User access permissions look ok in Enterprise Manager. Access is permit for my user.

Any suggestions?

Thanks

Permissions in SQL are much more than just access permit. You should also grant EXECUTE permssion for a stored procedure to a user if you want the user to execute the stored procedure; or you can create a role and add the user as member, then grant proper permissions to the role. To understand permissions related concepts in SQL, you can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_03_8q2b.asp

sqlsql

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>

Connection problem to MSDE

Hi,
I installed MSDE in my server and I have tried to connect
to my server over the Internet using Query Analyser and
Enterprise Manager. Both fail during the connection.
Testing the connection to MSDE locally in the server shows
me I can connect using the server
name "dedbxx\machinename", but I can't connect using the
server IP address.
Why is this happening?
I have already ran c:\Program Files\Microsoft SQL Server\80
\Tools\Binn\SVRNETCN.exe to configure the Net Protocols
supported by my server instance, including TCP/IP, but
nothing happens using IP to connect to the server.
Also tried to configure a ODBC entry in the server using
the IP address, but it shows me this message:
Coonection Failed:
SQLState: '01000'
SQL Server Error: 14
Coonection Failed:
SQLState: '08001'
SQL Server Error: 14
[Microsoft][ODBC SQL Server Driver][DBNETLIB] Invalid
connection
And here is the server log:
2004-04-29 13:02:08.28 server Microsoft SQL Server 2000 -
8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.2 (Build 3790: )
2004-04-29 13:02:08.32 server Copyright (C) 1988-2002
Microsoft Corporation.
2004-04-29 13:02:08.32 server All rights reserved.
2004-04-29 13:02:08.32 server Server Process ID is 208.
2004-04-29 13:02:08.32 server Logging SQL Server messages
in file 'D:\MSSQLMSSQL$WKS\LOG\ERRORLOG'.
2004-04-29 13:02:08.43 server SQL Server is starting at
priority class 'normal'(1 CPU detected).
2004-04-29 13:02:10.37 server SQL Server configured for
thread mode processing.
2004-04-29 13:02:10.39 server Using dynamic lock
allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2004-04-29 13:02:10.60 spid3 Starting up database 'master'.
2004-04-29 13:02:11.17 server Using 'SSNETLIB.DLL'
version '8.0.766'.
2004-04-29 13:02:11.17 spid5 Starting up database 'model'.
2004-04-29 13:02:11.26 spid3 Server name is 'DEDBxx\WKS'.
2004-04-29 13:02:11.26 spid3 Skipping startup of clean
database id 4
2004-04-29 13:02:11.35 spid5 Clearing tempdb database.
2004-04-29 13:02:11.84 server SQL server listening on
65.110.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
65.110.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
65.110.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
65.110.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
216.197.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
216.197.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
216.197.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
216.197.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
216.197.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
216.197.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
216.197.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
216.197.xx.xx: 1433.
2004-04-29 13:02:11.84 server SQL server listening on
127.0.0.1: 1433.
2004-04-29 13:02:12.45 spid5 Starting up database 'tempdb'.
2004-04-29 13:02:12.68 spid3 Recovery complete.
2004-04-29 13:02:12.68 spid3 SQL global counter collection
task is created.
2004-04-29 13:02:27.15 server SQL server listening on TCP,
Shared Memory, Named Pipes.
2004-04-29 13:02:27.15 server SQL Server is ready for
client connections
Any ideas?
Thanks,
Do you mean over the internet using a VPN?
If so, is the VPN giving you full access to the remote network?
Is the ServerIP address the public IP address, or the LAN IP?
Cheers,
James Goodman
"Rogerio" <anonymous@.discussions.microsoft.com> wrote in message
news:620e01c42e13$8e873b20$a301280a@.phx.gbl...
> Hi,
> I installed MSDE in my server and I have tried to connect
> to my server over the Internet using Query Analyser and
> Enterprise Manager. Both fail during the connection.
> Testing the connection to MSDE locally in the server shows
> me I can connect using the server
> name "dedbxx\machinename", but I can't connect using the
> server IP address.
> Why is this happening?
> I have already ran c:\Program Files\Microsoft SQL Server\80
> \Tools\Binn\SVRNETCN.exe to configure the Net Protocols
> supported by my server instance, including TCP/IP, but
> nothing happens using IP to connect to the server.
> Also tried to configure a ODBC entry in the server using
> the IP address, but it shows me this message:
> Coonection Failed:
> SQLState: '01000'
> SQL Server Error: 14
> Coonection Failed:
> SQLState: '08001'
> SQL Server Error: 14
> [Microsoft][ODBC SQL Server Driver][DBNETLIB] Invalid
> connection
> And here is the server log:
> 2004-04-29 13:02:08.28 server Microsoft SQL Server 2000 -
> 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.2 (Build 3790: )
> 2004-04-29 13:02:08.32 server Copyright (C) 1988-2002
> Microsoft Corporation.
> 2004-04-29 13:02:08.32 server All rights reserved.
> 2004-04-29 13:02:08.32 server Server Process ID is 208.
> 2004-04-29 13:02:08.32 server Logging SQL Server messages
> in file 'D:\MSSQLMSSQL$WKS\LOG\ERRORLOG'.
> 2004-04-29 13:02:08.43 server SQL Server is starting at
> priority class 'normal'(1 CPU detected).
> 2004-04-29 13:02:10.37 server SQL Server configured for
> thread mode processing.
> 2004-04-29 13:02:10.39 server Using dynamic lock
> allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
> 2004-04-29 13:02:10.60 spid3 Starting up database 'master'.
> 2004-04-29 13:02:11.17 server Using 'SSNETLIB.DLL'
> version '8.0.766'.
> 2004-04-29 13:02:11.17 spid5 Starting up database 'model'.
> 2004-04-29 13:02:11.26 spid3 Server name is 'DEDBxx\WKS'.
> 2004-04-29 13:02:11.26 spid3 Skipping startup of clean
> database id 4
> 2004-04-29 13:02:11.35 spid5 Clearing tempdb database.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 65.110.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 65.110.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 65.110.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 65.110.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 216.197.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 216.197.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 216.197.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 216.197.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 216.197.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 216.197.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 216.197.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 216.197.xx.xx: 1433.
> 2004-04-29 13:02:11.84 server SQL server listening on
> 127.0.0.1: 1433.
> 2004-04-29 13:02:12.45 spid5 Starting up database 'tempdb'.
> 2004-04-29 13:02:12.68 spid3 Recovery complete.
> 2004-04-29 13:02:12.68 spid3 SQL global counter collection
> task is created.
> 2004-04-29 13:02:27.15 server SQL server listening on TCP,
> Shared Memory, Named Pipes.
> 2004-04-29 13:02:27.15 server SQL Server is ready for
> client connections
> Any ideas?
> Thanks,
>