Mysterious login user failure

[saadabc]

I'm seeing the most wierd program behavior.

I'm connecting to sql server from a .NET application using SQL Server Authentication. My connection string is as follows:

"PERSIST SECURITY INFO=FALSE;INTEGRATED SECURITY=FALSE;DATA SOURCE=SACDBS01;USER ID=BarryB;PASSWORD=xxxxx;INITIAL CATALOG=WCI_Global;CONNECTION TIMEOUT=30"

I'm using a SQLConnection object to connect.

the SQLConnection is successful at one location in the application - but then I try it at another place in the application - it gives me the error : "login failed for user BarryB"

i'm using the same connection string - it's the same server, same database, same user - i've made sure that the user is in the logins for the server - and is included in the Users for that database - I've made sure that the username and password are correct.

I'm running a stored procedure once I make the connection to the sql server using Command.ExecuteNonQuery() . But the error is happening at the line:

myConn.Open()


Any ideas?

 

[ThatRickGuy]

Are you connection limited? Can Barry have two active connections? Can you use the same connection for both commands? Can you close the connection after the first command?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[saadabc]

actually - I'm sorry - I'm using the Command Object to build a SQLDataAdapter. The "login failed" error message occurs at

Dataadapter.Fill method

which is really wierd.

The

conn.open line executes successfully. If it was a login issue - wouldn't the connection fail.



here's the code.

Dim connString as String = "PERSIST SECURITY INFO=FALSE;INTEGRATED SECURITY=FALSE;DATA SOURCE=SACDBS01;USER ID=BarryB;PASSWORD=xxxxx;INITIAL CATALOG=WCI_Global;CONNECTION TIMEOUT=30"

Dim TempConnection as NewSQLConnection(connString)

Dim StoredProcedureName as String = "p_saEpicorSecurityCheck"

Dim TmpCmd As New SqlCommand(StoredProcedureName, TempConnection)

TmpCmd.CommandType = CommandType.StoredProcedure

If Arguments Is Nothing Then
'No parameters
Else
Call GetParams(TmpCmd, Arguments)
End If
Dim da As New SqlDataAdapter(TmpCmd)

da.Fill(ds, StoredProcedureName)

And it's failing at that line.

The stored procedure takes one argument and is defined as follows:



CREATE PROCEDURE p_saEpicorUserRights
(@status varchar(25) = 'All')

AS

.....


Go

 

[chmohan]

Dim StoredProcedureName as String = "p_saEpicorSecurityCheck"


shouldnt this be

Dim StoredProcedureName as String = "p_saEpicorUserRights"

 

[saadabc]

ok - i've tracked it down. It's giving the error in the Stored procedure itself.


The stored procedure is running a select query against a database on another server as follows:

SELECT * FROM sac_db3_srv.wci_corp.dbo.security_vw sv

I've made sure that the user barryb exists on sac_db3_srv and in the database wci_corp.

Also - earlier when I was trying to run the stored procedure (through the same application) it was giving me the error message that I needed to run sp_addlinkedserver to link the server to the existing one.

so i ran.

sp_addlinkedserver 'sac_db3_srv'

I don't know if i was suppose to identify a login with the addlinkedserver statement as well or not. A login (username,password) is certainly not in the arguments for sp_addlinkedserver .


so what do I do now.

 

[ThatRickGuy]

You might want to try on the SQL Server forum. If the issue lies between the linking of 2 seperate databases on one server...

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.