[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ErrorLog'.

[920506]

Hi, all

recently, I use the following script(somebody else) to create a database on a remote server: the script is as follow:
/*************************/
CREATE DATABASE [ErrorLog]
GO

Use ErrorLog

CREATE TABLE [dbo].[Errors] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SessionID] [char] (12) NULL ,
[RequestMethod] [char] (5) NULL ,
[ServerPort] [char] (5) NULL ,
[HTTPS] [char] (3) NULL ,
[LocalAddr] [char] (15) NULL ,
[HostAddress] [char] (15) NULL ,
[UserAgent] [varchar] (255) NULL ,
[URL] [varchar] (400) NULL ,
[CustomerRefID] [varchar] (20) NULL ,
[FormData] [varchar] (2000),
[AllHTTP] [varchar] (2000),
[ErrASPCode] [char] (10) NULL ,
[ErrNumber] [char] (11) NULL ,
[ErrSource] [varchar] (255) NULL ,
[ErrCategory] [varchar] (50) NULL ,
[ErrFile] [varchar] (255) NULL ,
[ErrLine] [int] NULL ,
[ErrColumn] [int] NULL,
[ErrDescription] [varchar] (1000) NULL ,
[ErrAspDescription] [varchar] (1000) NULL ,
[InsertDate] [datetime] NOT NULL
) ON [PRIMARY]



--Create the user "ErrorLog"

if not exists (select * from master.dbo.syslogins where loginname = N'ErrorLog')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'Navigator', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'ErrorLog', 'secret', @logindb, @loginlang
END
GO

if not exists (select * from dbo.sysusers where name = N'ErrorLog' and uid < 16382)
EXEC sp_grantdbaccess N'ErrorLog', N'ErrorLog'
GO

Grant select, insert On Errors to ErrorLog
/***************************/
in my *.asp program, I used the following string to connect to the database on the remote server.
/********
con.open "dsn=ErrorLog;uid=ErrorLog;pwd=secret;"
********/
the following message comes up:
/********/
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ErrorLog'.
/*********/
what is the problem here?
when I set up dsn ErrorLOG, I used "abc" userID and password "XXX" which is our server database administrator assigned to me. I tested connection in odbc, it is OK
I just don't get, ther user ErrorLog already had login id and granted access to database errorlog. any clue, please help!
Betty

[920506]

Hi,all
After I changed the connection string
to use the remote server loggin id and password, it works fine, but I just don't get it. Would somebody explain that to me?

Thanks
Betty

[rmiao]

I think it's because id/pwd conflict between dsn setting and your connection string.

[920506]

The userID and password assigned by our server administrator is for another database. I thought since I created the database errorLog and addlogin for errorlog and set password for this user, then definitely I can use this user to connect to remote user. But it doesn't work!
You are right, when I set up DSN, I did use userID and password our administrator assigned to me in order to connect to ErrorLog database. I just don't get it, why I cannot use UserID errorlog to connect to my database ErrorLog since I already created this user in this database, any explaination?
Thanks
Betty

[rmiao]

Should be able to. Try create another dsn with sql id errorlog and connect to sql using that new dsn.

[920506]

No, I cannot. I did it before and I did it again. I guess the userID and password my administrator assigned to me is for access the sql server, with that password, I can access any database on that sql server. Without that userID and password and only with UserID and password to ErrorLog database. It won't work just because of I don't have the key to the door and even I have the key to open the drawer. It just doesn't work. I am not sure if I am right.
Betty

[rmiao]

By the way, do you have permission to create sql logins? Did you assign proper permission to user errorlog in db errorlog? Seems the login is not created properly.