Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-02-13
1
Medium Priority
?
256 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
0
Comment
Question by:Caxxiux
1 Comment
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 2000 total points
ID: 23643060
#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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question