Trouble with Web Application DB Access After Upgrading from SQL2K to SQL2K5

We attempted an upgrade of SQL 2000 to 2005 without any troubles. Afterwards, we noticed that one of our application server started having troubles accessing the database. Our team started troubleshooting the problem and noticed that the problem lies in the custom code that was built with the application. Specifically speaking we are getting access denied and no user password will work--including the sa account.

We then tried looking at the code that executes access for users and noticed that the logins parser was still using the old Windows 2000 style login lookup (sys.X). We then proceeded to change it to sysLogin which should be the working version for 2005 but we are still having no luck users can't login through their browsers. Can someone please take a look at the code and advise what the correct code should be? I have attached a copy and pasted it for your perusal.

Many thanks!
Prince
<%
'=========================================================
' default.asp
' 
' Name              Date                 Description
' cstirling           10/26/04            created
' cstirling           11/14/05            updated for new fiscal year
' 
'=========================================================
 
response.expires = 0
response.expiresabsolute = now() - 1
response.addheader "pragma", "no-cache"
response.addheader "cache-control", "private"
 
if not request.form("loginUserName") = "" then
            username=lcase(Request.Form("loginUserName"))
            password=lcase(Request.Form("loginPassword"))
            Session("userName") = username
            
            Set cn = Server.CreateObject("ADODB.Connection")
            cn.ConnectionTimeout = Session("ConnectionTimeout")
            cn.CommandTimeout = Session("Commandtimeout")
            if ( (Session("username") <> "icu") and (Session("username") <> "mp3") _
                        and (Session("username") <> "***")and (Session("username") <> "***") _
                        and (Session("username") <> "***")and (Session("username") <> "***") _
                        and (Session("username") <> "***")and (Session("username") <> "***") _
                        and (Session("username") <> "***")and (Session("username") <> "***") _
                        and (Session("username") <> "***")and (Session("username") <> "***") _
                        and (Session("username") <> "***")and (Session("username") <> "***") _
                        and (Session("username") <> "***")and (Session("username") <> "***") ) then
            'Check username and password against Paragon master
                        cn.Open "Provider=SQLOLEDB; Data Source=***HOSTNAME***; Initial Catalog=master;User Id=************; Password=**************;"
                        Set cmd = Server.CreateObject("ADODB.Command")
                        Set rs = Server.CreateObject("ADODB.RecordSet")
                        cmd.CommandText = _
                                    "SELECT name from syslogins " & _
                                    "WHERE " & _
                                                "name= '" & username & "' AND " & _
                                                "pwdcompare('" & password & "',password) = 1"
                        cmd.Commandtype = 1
                        Set cmd.ActiveConnection = cn
                        rs.Open cmd
            
                        if rs.EOF then 'security failed
                            myStr = _
                                                "<HTML><BODY BGCOLOR=FFFFFF LINK=navy VLINK=navy>" & Chr(10) & _
                                                "<h1>Login Failed</h1>" & Chr(10) & _
                                                "<p>Please check your username and password. Click <a href=default.asp>here</a> to retry." & Chr(10) & _
                                                "<p>If you have questions concerning user accounts please email: <a href=mailto:webmaster@mycompany.com><i>webmaster@mycompany.com</i></a>" & Chr(10) & _
                                                "</BODY></HTML>"
                                    Response.Write(myStr)
                                    Session.Abandon 'clear all session vars
                                    Response.End
                        else
                                    strSQL = "select * from lgh_users where user_login = '" & RS("name") & "'"
                                    set permissionsRS = getLGHRS(strSQL)
                                    if not permissionsRS.EOF then
                                                session("userName") = permissionsRS("user_login")
                                                session("userLogin") = permissionsRS("user_login")
                                                session("userFname") = permissionsRS("user_fname")
                                                session("userLname") = permissionsRS("user_lname")
                                                session("userEmail") = permissionsRS("user_email")
                                                session("userStatus") = permissionsRS("user_status")
                                                session("userEmpID") = right("00000" & permissionsRS("user_empid"), 5)
                                                user_id = permissionsRS("user_empid")
                                                strPERMISSION = "select mgr_dept, mgr_level from lgh_mgrrelas where mgr_empid = " & user_id & " order by mgr_dept"
                                                set rsPERMISSION = getLGHRS(strPERMISSION)
                                                if not rsPERMISSION.EOF then
                                                            session("mgrDept") = rsPERMISSION("mgr_dept")
                                                            rsPERMISSION.movenext
                                                            do while not rsPERMISSION.EOF
                                                                        session("mgrDept") = session("mgrDept") & ", " & rsPERMISSION("mgr_dept")
                                                                        rsPERMISSION.movenext
                                                            loop
                                                else
                                                            session("mgrDept") = "0"
                                                end if
                                    end if
                        end if
            Else
                                    myStr = _
                                    "<HTML><BODY BGCOLOR=FFFFFF LINK=navy VLINK=navy>" & Chr(10) & _
                                    "<h1>Login Failed</h1>" & Chr(10) & _
                                    "<p>Please enter your <EM>personal</EM> username and password. Click <a href=default.asp>here</a> to retry." & Chr(10) & _
                                    "<p>If you have questions concerning user accounts please email: <a href=mailto:webmaster@mycompany.com><i>webmaster@mycompany.com</i></a>" & Chr(10) & _
                                    "</BODY></HTML>"
                        Response.Write(myStr)
                        Session.Abandon 'clear all session vars
                        Response.End
            end if
            
            cn.Close
            Set cn = Nothing
            response.redirect("default.asp")
end if
toolName = "Status Alerts"
%>
<!-- #include VIRTUAL="/resources/includes/global_inc.asp"-->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
            <title>LGHWeb - Status Alerts</title>
            <!-- #include VIRTUAL="/resources/includes/style_inc.asp"-->
            <style>
                        .label {
                                    background-color : #CCCCCC;
                                    font-weight:        bold;
                                    TEXT-ALIGN: right;
                        }
            </style>
</head>
 
<body leftmargin="0" topmargin="0" rightmargin="0" bottommargin="0" marginwidth="0" marginheight="0">
 
            <!-- #include VIRTUAL="/resources/includes/page_begin.asp"-->
            <!-- CONTENT BEGINS -->
            <% if session("username") = "*******" or _ %> 
            <% session("username") = "*********" or _ %> 
            <% session("username") = "*********" or _ %> 
            <% session("username") = "***********" or _ %> 
            <% session("username") = "**********" or _ %> 
            <% session("username") = "*********" or _ %> 
            <% session("username") = "*********" then %> 
            <table width="100%" border="0" cellspacing="5" cellpadding="5">
                        <tr>
                                    <td>
                                                <div align="center">
                                                            <h3>System Status - Edit</h3>
                                                            <h4><%=now%></h4>
                                                </div>
                                                
                                                <ul>
                                                            <strong>Admin View</strong>
                                                            <li><a href="editValues.asp">Edit Values</a></li>
                                                </ul>
                                    </td>
                        </tr>
            </table>
            <% elseif session("username") <> "" then %>
                        <table width="500" border="0" cellspacing="2" cellpadding="10" align="center" bgcolor="#FF3333">
                                    <tr>
                                                <td bgcolor="#FFFFFF">
                                                <br>
                                                <strong>
                                                You have logged in, but do not have permissions to this application.
                                                Please contact [PCM] ****** in Finance for permission to access this tool.
                                                </strong>
                                                <br><br>
                                                </td>
                                    </tr>
                        </table>
            <% else %>
            <strong>Please login with your PatWeb username and password:</strong>
            <br><br>
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
                        <tr>
                                    <td width="30">&nbsp;</td>
                                    <td>
                                                <table width="200" border="0" cellspacing="1" cellpadding="7" align="left" bgcolor="#000000">
                                                <form action="adminLogin.asp" method="POST" name="myForm">
                                                            <tr bgcolor="#DDDDDD">
                                                                        <td colspan="2"><strong>Please login...</strong></td>
                                                            </tr>
                                                            <tr bgcolor="#EEEEEE">
                                                                        <td>Username:</td>
                                                                        <td><input type="text" size="20" name="loginUserName" value="<%=Session("UserName")%>"></td>
                                                            </tr>
                                                            <tr bgcolor="#EEEEEE">
                                                                        <td>Password:</td>
                                                                        <td><input type="password" size="20" name="loginPassword"></td>
                                                            </tr>
                                                            <tr bgcolor="#EEEEEE">
                                                                        <td colspan="2" align="center">
                                                                                    <input type="submit" name="submit" id="submit" value="  Login  ">
                                                                                    &nbsp;&nbsp;
                                                                                    <input type="reset" name="reset" id="reset" value="  Reset  ">
                                                                        </td>
                                                            </tr>
                                                </form>
                                                </table>
                                    </td>
                        </tr>
            </table>
            
            <% end if %>
            <!-- CONTENT ENDS -->
            <!-- #include VIRTUAL="/resources/includes/page_end.asp"-->
 
</body>
</html>

Open in new window

Copied-Code-Culprit.txt
CaxxiuxAsked:
Who is Participating?
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
#1. you have referenced syslogins and it is still there in master for backward compatibility, so that should not be the problem.

#2. you use pwdcompare('somepassword') this is undocumented, but a url that you could learn more about this is http://blogs.msdn.com/lcris/archive/2007/10/31/sql-server-undocumented-password-hashing-builtins-pwdcompare-and-pwdencrypt.aspx, and you probably should change the pwdcompare to read

"SELECT name from syslogins " & _
       "WHERE " & _
       "name= '" & username & "' AND " & _
       "pwdcompare('" & password & "',password, 1) = 1"

The third parameter says to check for a pre-SQL2000 password hash.  So you may even use the pwdcompare('something', password) = 1 OR pwdcompare('something', password, 1) = 1 to ensure that you get both.

Otherwise you will want to change the password using SQL 2005 to the current password so that it rehashes it with SQL 2005 and you should not have to use the third parameter on pwdcompare().

Just read the warnings on the page in the url above, they are to be understood.

Lastly.  You should be very aware of SQL Injection your site.  You trust the input to the username and password and you blindly put them in the SQL String which anyone could bypass with the right statements.  Be careful.

.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.