We help IT Professionals succeed at work.

.net SQLDataAdapter using domain credentials...

530 Views
Last Modified: 2013-12-16
I found a how-to on the internet concerning a reuseable SQL Data Adapter web part for Sharepoint.  I edited it to make it match my needs and I have one small issue that I just cannot figure out.  It works fine with a database user account but will not work using a domain account.  I'm sure it has to do with how the text is passed in the username property but I cannot figure it out.  I'm posting the code to both of my Classes in my project.  The first is my dataviewer (GFDataViewer.cs) and the second is my connection class (DBAccess.cs).  I'm hoping someone with more experience can see what I am doing wrong.

Thank you in advance,
GF
//GFDataViewer.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data;

namespace GFDataViewer
{
    public class GFDataViewer : WebPart
    {
        protected override void RenderContents(HtmlTextWriter output)
        {
            output.Write(username);
            output.Write(innerpassword);
            output.Write(dbname);
            output.Write(servername);
        }
        private string servername;
        [Personalizable(), WebBrowsable(true), WebDisplayName("SQL Server"), Category("Data Properties")]

        public string ServerName
        {
            get { return servername; }
            set { servername = value; }
        }

        private string dbname;
        [Personalizable(),WebBrowsable(true),WebDisplayName("Database Name"), Category("Data Properties")]

        public string DatabaseName
        {
            get { return dbname; }
            set { dbname = value; }
        }

        private string username;
        [Personalizable(), WebBrowsable(true), WebDisplayName("DB User Name"), Category("Data Properties")]

        public string UserName
        {
            get { return username; }
            set { username = value; }
        }

        private string password;
        [Personalizable(), WebBrowsable(true), WebDisplayName("Password"), Category("Data Properties")]

        public string Password
        {
            get { return password; }
            set { password = value; }
        }

        private string innerpassword;
        [Personalizable(), WebBrowsable(false), WebDisplayName("InnerPassword"), Category("Data Properties")]

        public string InnerPassword
        {
            get { return innerpassword; }
            set { innerpassword = value; }
        }

        private string sqlquery;
        [Personalizable(), WebBrowsable(true), WebDisplayName("SQL Query"), Category("Data Properties")]

        public string SQLQuery
        {
            get { return sqlquery; }
            set { sqlquery = value; }
        }

        private int datarows;
        [Personalizable(), WebBrowsable(true), WebDisplayName("Rows Per Page"), Category("Data Properties")]

        public int DataRows
        {
            get { return datarows; }
            set { datarows = value; }
        }

        GridView gv = new GridView();

        protected override void CreateChildControls()
        {
            base.CreateChildControls();
            try
            {
                DBAccess objDB = new DBAccess();
                if (password != "******")
                {
                    InnerPassword = password;
                    password = "******";
                }
                string strConnection = "UID=" + username + ";PWD=" + innerpassword + ";Initial Catalog=" + dbname + ";Data Source=" + servername + ";";
                string sql = sqlquery;
                objDB.StrConnection = strConnection;

                DataTable dt = new DataTable();
                dt = objDB.GetSQLResult(sql);

                Controls.Add(gv);
                gv.DataSource = dt;
                gv.AlternatingRowStyle.BackColor = System.Drawing.Color.WhiteSmoke;
                gv.GridLines = GridLines.Horizontal;
                gv.CellPadding = 1;
                gv.DataBind();
                gv.HeaderStyle.BackColor = System.Drawing.Color.LightGray;
                gv.AllowPaging = true;
                gv.PageSize = datarows;
                gv.PageIndexChanging += new GridViewPageEventHandler(this.gv_PageIndexChanging);
            }

            catch (Exception ex)
            {
                Label lbl = new Label();
                lbl.Text = "Error Occured: ";
                lbl.Text += ex.Message;
                Controls.Add(lbl);
            }
        }
        protected void gv_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gv.PageIndex = e.NewPageIndex;
            string strConnection = "UID=" + username + ";PWD=" + innerpassword + ";Initial Catalog=" + dbname + ";Data Source=" + servername + ";";

            DBAccess objdb = new DBAccess();
            objdb.StrConnection = strConnection;
            DataTable dt = objdb.GetSQLResult(sqlquery);
            gv.DataSource = dt;
            gv.DataBind();
        }
    }
}

//DBAccess.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace GFDataViewer
{
    public class DBAccess
    {
        private string strConnection;
        public DBAccess()
        {
        }
        public string StrConnection
        {
            get { return strConnection; }
            set { strConnection = value; }
        }
        public DataTable GetSQLResult(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection cnn = new SqlConnection(strConnection);
                SqlDataAdapter da = new SqlDataAdapter(sql, cnn);
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                return dt;
            }
        }
    }
}

Open in new window

Comment
Watch Question

first can you confirm you are able to coonect to sqL server db using windows auth. over a management studio.


thanks

Author

Commented:
Yes I can connect from my workstation to the server using my domain account.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you for the quick reply.  I understand the trusted connection and how that works.  however since my goal is to make a "reuseable" web part the user can put this on any sharepoint page and fill in for any db server and db they want to query.  So I don't want to add every sharepoint server apppool account to every db.  I thought it was just the way the part was passing the username.  I mean a normal connection string will work with a domain account.
the normal connection string does now allow windows authentication only SQL authentication.

Author

Commented:
:(  u are right.  So for now until I can figure something else out I gave the users local sql accounts to use for their respective servers.  Thanks for your insight George.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*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.