Consistant MS SQL Server 2005 Login Failures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Consistant MS SQL Server 2005 Login Failures

Author

Topic

TEDSMITH
Starting Member

9 Posts

Posted - 2010-01-28 : 05:41:46

If you can fix this, you're a genius!

I am trying to recreate a friends website within a virtual machine.I have written down much of what I have tried as I've been going along, however, I did forget to document some things so I might have tried one or two other things besides what's written below.

His server was running Windows Server 2003 using IIS6 and his website is ASP based.

So I have :

Installed Windows Server 2003 and all service packs. Its IP address is set to 192.168.0.1

Created a Windows user (PeterPan) that matches the database username of the SQL 2005 database. I have also enabled read & write permission for this user of the C:\MS SQL Server... folder.

Enabled IIS and the IUSR user account given read and execute access to the directory containing the website files in c:\Inetpub\MyWebsite. In IIS Manager, right clicked the website, clicked Properties. Clicked on 'Home Directory' tab. Clicked 'Configuration' button. Selected Options tab and ticked 'Enable Parent Paths' to allow ASP 'include file' statements.

Updated .NET Framework from 1.1 to 2.0 (as ver 2.0 was running on his server)
Installed MS SQL Server 2005 Enterprise Edition

Then configured MS SQL Server :

Named Instance : MSSQLSERVER
Built-in System Account used.
Initially, Windows Authentication Mode enabled but then changed to 'SQL Server and Windows Authentication Mode'. Restarted after changes.
Default SQL Collation
All other potential options enabled for maximum operatibility.
PeterPan user account added to all of the SQL... user groups

SQL Server Configuration Manager launched
Right clicked 'SQL Server Network Administration'
Protocols for MSSQLSERVER
Shared Memory - Enabled
Named Pipes – Enabled
TCP/IP - Enabled (have also tried disabled)
VIA - Disabled

(Enabling all caused SQL Server login problems after reboots)

SQL Native Client Configuration --> All 4 client protocols enabled
Aliases -->

Then imported an MSQL.bak backup into MS SQL Server on my virtual machine.

Enabled Active Directory Services and defaults accepted

Then incorporated ASP into the MS SQL 2005 Server system by executing C:\WINDOWS\Microsoft.NET\Framework\v2.0.XYZ\aspnet_regsql.exe and configured it using defaults

ASP.NET State Service was then adjusted to start automatically. Its stopped by default and is needed for ASP pages.

Then went to 'Add\Remove Programs ? Add Windows Components ? Application Server (Details button)' and enabled 'ASP.NET'

Then executed the following SQL :

USE master
CREATE LOGIN [ServerName\DatabaseName]
FROM WINDOWS
WITH DEFAULT_DATABASE = DatabaseName

The database itself already contains the user PeterPan from the earlier restore.

Guest account enabled.

Disabled “Show friendly HTTP error messages” to enable more explanatory error messages in IE.

I have then edited the database connection details of his ASP pages so that the specified connection settings match the new virtualised environment.

In SSCM(SQL Server Configuration Manager)
Clicked Protocols for MSSQLSERVER
Double Clicked TCP/IP
Listen To All ---> No

For IP1
Active = Yes
Enabled = Yes
IP Address = IP of the DB Server - 192.168.0.1
TCP = Left blank
TCP Port = 1433

Firewall is disabled.

Saved everything and Re-started the SQL Server service

Despite all of that, whenever I try to launch his website at http://localhost I get the following error:

Microsoft SQL Native Client error '80040e4d'
Login failed for user 'PeterPan'

/subdirectory1/testfile.asp, line 10

Line 10 is :

dbconn.Open strConn

The value of strConn is :

strConn = "Provider=SQLNCLI;Server=192.168.0.1;Database=DatabaseName;UID=PeterPan;PWD=Password"

I have read the following threads that have helped me get this far. I have read tohers besides that were not so useful. :

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22835564.html
http://forums.aspfree.com/microsoft-iis-12/asp-pages-not-working-on-iis-6t-131485.html
http://msdn.microsoft.com/en-us/library/ms188670.aspx
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_22738400.html
http://www.eggheadcafe.com/forumarchives/SQLServersetup/Feb2006/post25454248.asp
http://support.microsoft.com/kb/889615

Please can someone help? I am at the end of my teather and fed up of reading about people where the above solutions have worked :-(.

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 06:49:14

Dunno if it makes any difference, but my connection strings look more like this:

strConn = "Provider=sqloledb;Data Source=192.168.0.1;Initial Catalog=DatabaseName;User Id=PeterPan;Password=YourPassword"

More info on connection strings here:

www.connectionstrings.com/

its not on your list, so hopefully not on your "I have read tohers besides that were not so useful" list

TEDSMITH
Starting Member

9 Posts

Posted - 2010-01-28 : 07:19:04

Many thanks for the tip.

I have just tried all of the variety of different ways but unfortunately they all result in either the same error or different errors. For example, it didn't recognise 'Trusted_Path=False' etc.

I have just tried adding the Administrator account of the server to the specific database and then changed the connection string accordingly. That doesn't work either. So it leads me to suspect it perhaps is not the specific account that is causing the problem - rather something else to do with MS SQL Server?

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 07:58:30

"SQL Native Client Configuration --> All 4 client protocols enable"

Suggest disabling all BUT tcp/ip

"The database itself already contains the user PeterPan from the earlier restore"

That won't have automatically been re-associated with the new Server login. I think the command you need is:

EXEC MyDatabaseName.dbo.sp_change_users_login 'Update_One', 'PeterPan', 'PeterPan'

Try to login, on the server, to SSMS using the PeterPan credentials.

If you manage to login try a simple "SELECT TOP 10 * FROM MyDatabaseName.dbo.MyTable" to check that you can "see" the database's tables.

Get that working first, then attempt connection from the App.

You might need to set "Allow Remote Connections" somewhere - although if I understand it the App is on the same machine as SQL server? If so I think its just that the UserID is not getting you to the database.

TEDSMITH
Starting Member

9 Posts

Posted - 2010-01-28 : 08:54:30

OK, tried that. Error returned in console is :

Msg 15291, Level 16, State 1 Procedure sp_change_users_login, Line 131
Terminating this procedure. The Login name 'PeterPan' is absent or invalid

In DataBaseName --> Security --> Users, PeterPan is listed though?

I am going to Google but hoped you might read this response and know an answer off the cuff?

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 09:09:56

Sounds like you don't have a SERVER login (only a Database login, that will have been restored with the DB).

I inferred from
"USE master
CREATE LOGIN [ServerName\DatabaseName]
FROM WINDOWS
WITH DEFAULT_DATABASE = DatabaseName

The database itself already contains the user PeterPan from the earlier restore. "

that you had made a PeterPan server login ...

EXEC sp_addlogin 'PeterPan', 'password', 'MyDatabaseName'

Then the dbo.sp_change_users_login command

TEDSMITH
Starting Member

9 Posts

Posted - 2010-01-28 : 10:19:50

OK, we're getting there mate! New error :

Microsoft SQL Native Client error '80040e09'
SELECT permission denied on object 'TableName', database 'DatabaseName', schema 'dbo'

/subdirectory1/testfile.asp, line 13

I have navigated to DatabaseName --> Tables --> TableName, right clicked its properties. All of the boxes were blank and no user was in it, so I added my user PeterPan and ticked all the boxes and refreshed the database. Error is repeated?

If you have any more advice I'd love to hear it as I feel I am nearly there!!

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 10:29:46

I suggest you grant PeterPan the (system-provided) database role db_DataReader - that will allow it to Select any table. You can revoke that role later and set up something more specific

Answered a similar question about Roles / permissions earlier on today, which may fill-in-some-blanks

:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138945#543014

TEDSMITH
Starting Member

9 Posts

Posted - 2010-01-28 : 11:06:43

I already have. ?? I have ticked every box in 'Owned Schemas' and 'Role Members'. db_datareader appears ticked in 'Role Members' for the user PeterPan for the database DataBaseName.

So I right clicked DatabaseName --> Security --> Roles --Database Roles --> New Database Role. Gave it a name, specified PeterPan' as the Owner, and then Ticked db_datareader. Restarted database server. Same error!!!

I am going to jump off a bridge at this rate!

TEDSMITH
Starting Member

9 Posts

Posted - 2010-01-28 : 11:21:14

Done it....right clicked the 'Security' --> 'Logins' --> PeterPan and ticked all the 'Server Roles'.

Now it works.

Thank you so much for your help mate. You have saved me!

TEDSMITH
Starting Member

9 Posts

Posted - 2010-01-28 : 11:21:15

Done it....right clicked the 'Security' --> 'Logins' --> PeterPan and ticked all the 'Server Roles'.

Now it works.

Thank you so much for your help mate. You have saved me!

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 11:26:21

"I have ticked every box in 'Owned Schemas' and 'Role Members'."

Just to be sure ... that is likely to include some DENY permissions too ... they will prevent access (Dunno which DENY takes preference over which SELECT though ...)

You shouldn't need any Owned Schema (other than PeterPan itself, but I don't think you can unselect that one anyway)

The DataBase Roles should be:

db_DataReader
db_DataWriter

(these will allow Read/Write to ALL tables - which will do for now)

Turn all the other roles off (unless you recognise any that you explicitly added originally)

If needs be create a new Server Login for PeterPan2, and set up these minimal roles for that, new, user. Then try logging in (to SQL Server management Studio - don't try connecting from your APP as yet) using those credentials, and try a simple

USE MyDatabaseName -- Or make sure that the correct database is selected & "current"
GO

SELECT TOP 10 * FROM MyTable

Before trying to connect from your APP we need to be sure the User has the right access. Easier to do that in SSMS 'coz we can be fairly certain that that works the way it should!

If that works then try changing the connection details to use PeterPan2 and try from your app.

Plan B (if that fails!) change PeterPan2 SERVER ROLE to be SysAdmin. That should provide GOD access. Don't leave it like that if it works!! (This may not work on PeterPan [orginal] because you may have some DENY permissions, which may take precedence)

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 11:29:47

Crossed with my reply ...

"Done it....right clicked the 'Security' --> 'Logins' --> PeterPan and ticked all the 'Server Roles'."

OK, that's presumably given you "God" SysAdmin, which is overriding everything else.

Suggest you make PeterPan2 as per my earlier message, and try connecting with that, to work out what you NEED to grant.

Try granting the minimum and working up. You probably don't know all the things you granted to PeterPan [original] and therefore that login is now probably a bit of a security risk!!

TEDSMITH
Starting Member

9 Posts

Posted - 2010-01-29 : 08:14:09

Thanks a lot. You're a top quality egg.

I used a Search & Replace facility to change all the database connection strings for all of the ASP files and the site is fully functional apart from the admin section, unfortunately. I get the same connection problems with just this one area. After I click the submit button to login it says

MS SQL Native Client '80004005'. SQL Network Interfaces: Error locating Server\Instance Specified [xFFFFFFFF]

NewFile.asp, Line 7

I got to Line 7 and it says

con.Open application("DBCon")

Just above that line it says :

dim con,rs,qry
set con = server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.Recordset")

Why would this not work, but the other connection string does work?

(Please forgive me - I do not use ASP or MS SQl usually and I am merely trying to demo this website to some colleagues. Security is not a concern as it is not connected to the Internet and its running in a virtual machine)

Ted

TEDSMITH
Starting Member

9 Posts

Posted - 2010-01-29 : 09:23:17

Ah - got it. Global.asa file contains the database string values for the connection variable. Ignore me with that one.

I no longer get an error - instead it just loops me back round when I try to login. Possibly a javascript restriction or something. Anyway, I think re SQL, I'm sorted, so thanks again

Ted

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 09:35:42

"Global.asa file contains the database string values for the connection variable"

Shoot someone! I always find a public execution good for that sort of thing. They should have an INCLUDE file so the connect string is only in one place.

Glad you got it half-working - assuming that is progress!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources