We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

Medium Priority
270 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

SQL Server Architect
CERTIFIED EXPERT
Commented:
#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.

.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.