Troubleshooting A New Application Role
Hi all,
This one is a real X FIle, just without Mulder, Scully or the Lone Gunmen!
I have a database, to which access must be restricted via a sole application. So, I have to use an application role. I go in the database and run these statements to add and activate the roles, respectively;
Exec sp_addapprole 'Sirius', 'password'
(The system confirms the role is created.)
Exec sp_setapprole 'Sirius', 'password' 'odbc'
(The system confirms the role is activated.)
Right, now I should not be able to connect using anything but this role, agreed? But here's where things go wrong. I can then successfully connect from another computer by using MS query from Excel, from a login that is not even a member of the Public Role!
I tried again, started and stopped the Server/DTS/Agent services and dropped the old role after each successful login before recreating it. I've checked my syntax exhaustively. I must be doing something wrong, or overlooking something, otherwise MS has a major security problem! (Just hope the Cancer Man doesn't find out!!)
Thanks in advance everyone,
Jaishel.
View Complete Forum Thread with Replies
Related Forum Messages:
Application Role, App Role
Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) for the given connection string Dim connstring As String = "Data Source=Northwind;Initial Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;" Dim cmd As SqlCommand Dim param As SqlParameter Dim cookie As Byte() Dim cn As New SqlConnection(connstring) If (cn.State = ConnectionState.Closed) Then cn.Open() End If Please help.. Thanking you, Nirmala
View Replies !
Application Role
I am confused . What is considered an application and how SQL would know ? If I have a web site accessing SQL VIA IIS will SQL Server treat it as an application ? How about MS Excel ? Also , If I was to use the application of Power builder , using app role , how do control which user can use the app ? Thanks
View Replies !
Application Role
With reference to Mr. Eric Charron's article about Implementing Application Roles in SQL7, I have used this tips in my application. But I am facing the following problems. I am looking for some solution from this User's Group. Problem: I am using application (VB6/SQL7) role. I have number of reports. To display reports I follow the following steps. 1) Open Connection 2) Set application role 3) Execute procedure for the report 4) Close the connection Since I have no. of reports, I go through the aboue steps no. of times. For first 2 times it works fine but 3rd time, it gives error ie. application role not set properly. RM Joseph
View Replies !
Application Role
hi all ! first time i created application role with read and write permissions , once application accessing automatically it assigns rd and wr permissions to that users or previously wht permissions are there in logins will it be reflected to that application role. once we create the appliation role , is there any changes we have to do in coding part pls clear my doubt. regards manoj
View Replies !
Security For Application Role
We have an application use Approle to read from database. If the client login to windows as administrator or a name that has the administrator rights, the application can get all data. If the client login to windows as a domain user that has limited rights, the application can't get all data. I run profiler and found that it seems, when application use approle to access a database, the login name is the domain user that log into windows. Is there anybody know what type of right the window login name should have in order to get all data from a database? Second question, when I log in to window as domainusername( username is not administrator, but has administrator rights). In the profiler, I can see the application use this domainusername access database. However, under sql server login node, I didn't find domainusername. Is this because, the domainusername belongs to buildinadministrator? Thanks
View Replies !
Application Role And SSRS
Hi dear reader I made an application that uses a Sql Server 2005 Express DataBase. In the database I made a application role. When the user logs into my application I run this procedure: If Not sqlConnectionCR Is Nothing Then If Not sqlConnectionCR.State = ConnectionState.Open Then sqlConnectionCR.Open() SqlConnection.ClearAllPools() ConsultasSqlCommand = New SqlCommand ConsultasSqlCommand.CommandType = CommandType.Text ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap" ConsultasSqlCommand.Connection = sqlConnectionCR ConsultasSqlCommand.ExecuteNonQuery() End If Else.... I understand that this procedure connects to my sqlserver database as my application role Ok, so far no problems in reading and manipulating data. The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access.... I think that is because the Server Report uses the user account (domain/user) to read the database. No user (besides admin) has access permissions in the database (only admin and application role). So, my cuestion is: How can I tell Report Server to use the application role to display reports? Thank you for your time and help. Giber
View Replies !
BULK INSERT And APPLICATION ROLE
I want to add bulkadmin permission to my applicatio role. Is it aposible.My windows account havo only public permission on database.I'm using application roleEXEC sp_approlepassword 'MyRole', 'password';Therefore I want to BULK some data with BULK INSERT command.Error is:The current user is not the database or object owner of table'tablename'. Cannot perform SET operation.Thanks in advance.
View Replies !
Use SQL 7.0 Application Role With Crystal 8.0 Crpe32.dll
using vb6 and crystal global32.bas to make function calls to the crpe32.dll and there doesn't seem to be any calls to pass the application role id with password. ap roles must be passed at the ad hoc level, which means i have to execute the system stored procedure for the ap role thru crystals connection to the sql server. HELP....seagate has tried to help, but want me to use odbc dsn, which is exactly what i'm trying to avoid. oledb provider works like a champ, except for app roles with crystal.
View Replies !
Connecting To A Database From VB Using An Application Role
I know how to create an Application role in SQL server 7. Now how do I connect to the database from VB using that Application Role? I can't find anything about this topic anywhere. Is this the purpose of an Application role or am I way off? Thanks for the help Steven Abt StevenA@grsgroup.com
View Replies !
Application Role And SQL Express (2005)
Hello, Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)? Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005 Thanks John
View Replies !
Create Database With Application Role...
Hi, I want my application to create database and I do the following things: 1)Create application role 2)Grant create database to application role 3)Activate application role 4)Create database and I get the answer: CREATE DATABASE permission denied in database 'master'.
View Replies !
Application Role Losing Connection?
Hi, I'm testing the use of application roles for security. The customer I work for has still a lot of ASP intranet applications running. We're migrating the databases to a SQL Server 2005 server. I've changed the connection string to a user without any permissions but to log on. After that I use an application role for permission to select different tables and to execute Stored Procedures. The first queries do execute but after that I get "Permission denied", like I haven't got the application role anymore. Any ideas? Adrian
View Replies !
Web Application And Role Management In Sql Server 2005
Hi every body I'm developing a web application and i like to use the sql server 2005 role management features istead of developing a role management package in my program, I can do it on my tables and othe database items but I have no idea about using database access rights in my web pages to permit some one viewing or updating a web form... Is there any system table or system stored procedure showing access rights in my data base? or is there another idea to do this?? by Thanks Javaneh
View Replies !
SQL Server 2005 Application Role's Problem
Hi all. I have developed a VB6 program which will activate an Application Role to UPDATE and INSERT some tables in SQL Server 2005. My program will login with a username, say USER and then run with the rights of the Application Role which will SELECT, UPDATE and INSERT the following tables: 1) Table A 2) Table B 3) Table C The USER login is a restricted user which has only SELECT permission to Table A, B and C. I encounter a problem in which my program can only UPDATE table B and table C but Table A. I have double checked the settings of Table A, Table B and Table C. Theirs are all same. Fyi, the column which can't be updated is of type "datetime". Once i grant USER login UPDATE permission to Table A, my program works perfectly in which it can UPDATE all the tables including Table A. I have tried for almost 2 days but am still clueless. Any ideas? Thank you so much.
View Replies !
Sending SQL 2005 Dbmail When Using Application Role
I am attempting to use dbmail from an application that logs in to my database using an application role. Since the application role does not exist outside the database, I created an spSendEmail in the database with "execute as login='mailagent'" in it: I set the database to trustworthy, created a 'mailagent' account and added it to the msdb database with the databasemailuser role rights. Email works just fine on the server when I use the execute as login='mailagent' to fire off the msdb..sp_send_dbmail. But from the .NET application, I get the error: "Cannot execute as the server principal because the principal 'mailagent' does not exist, this type of principal cannot be impersonated, or you do not have permission." When I run my spSendEmail stored procedure from the calling database, I get the same error.
View Replies !
Get Sql Err Message 15422 When Activating Application Role.
Running VB 2005 Express Edition and Sql Server 2005 Express Edition (SQLX). Developing a desktop application which calls a local instance of ".sqlexpress". This app needs to set data base options and add/del various table columns. When activating the application role, I get the following message: HariCari SQL Error/s 15422 - Application roles can only be activated at the ad hoc level. Anyone know what this message means? I have searched SQL Server Books On-Line and been unable to find a list of Sql err numbers. Either I have missed the obvious or Books On-Line has missed the obvious. Thanks Gary
View Replies !
Login Failed For 'user' (Application Role)
I've created a database in SQL Express and I have a Windows form attempting to connect to it through SQL Authentication. Connection string: private string connString = @"Data Source=.sqlexpress;Initial Catalog=SQLTestDatabase;User ID=SearchAppRole; Password=password;"; The role I have added to the database is an Application Role. It has been added to the Database permissions with Grant checked for "Select" and "Authenticate". If I test this with query analyzer, it returns expected results (if I remove Grant from 'Select', it fails) sp_setapprole 'SearchAppRole', 'password' select * from recipe If I edit my connection string (for testing purposes) to use the sa account, the application can connect and run the Select statement: private string connString = @"Data Source=.sqlexpress;Initial Catalog=SQLTestDatabase;User ID=sa; Password=sa_password;"; However, I cannot get the application to successfully logon and run the select statement when using the user id and password of the Application Role. I get error: System.Data.SqlClient.SqlException: Login failed for user 'SearchAppRole'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj I can't find much information on Application Role...I just want one basic permission for the application as a whole. Any help is appreciated. Thanks.
View Replies !
Application Role How Query A View Whose Reference Table Is In Another Db?
Hi everybody. I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1 I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)). With the following query SELECT USER_NAME() I see that the approle is being used. Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error: The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context. What should I do to make it work? The table in DB2 has the same schema of the view in DB1 which refers to it. I put the DB1 TrustWorthy and both the database have the db_chaining option activated. Any idea on how to solve the problem would be widely appreciated. Thank you very much. Vania
View Replies !
SQL Server 2000 And 2005 - Application Role - Sp_setapprole
Hi All, Is there any limitation for setting password to an approle (like it should be 8 character long, should not start with numbers) ? If so, what are those limitations ? Does the same is applicable for sp_setapprole which uses the same password ? please confirm, for both SQL Server 2000 and 2005 versions. thanks in advance, Regards, Kailai
View Replies !
Uploading SQL Express To SQL Server 2005 - Role's Causes Application Error With SSE Provider
I am very frustrated. Everything works on the local host but when I upload to server I can login to the admin role I created, but when I try to access pages that have role priveleges I get the following error: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. The ASPNETDB.MDF database was uploaded using the Database Publishing Wizard. Please help!
View Replies !
Troubleshooting
Hi list, I'd be grateful for a little advice. Running into some problems and not sure where to start. I've looked through some books and articles and Online Help but need some insight into the issue from a person. I'm running a SQL2K server with about 30 databases, none of them over 20M in size. These db's are driving websites on servers in the same server room and also accessed remotely using Enterprise Manager. For 95% of the time these databases are performing well. Then connections on webpages start timing out. This is a problem that 'fixes' itself very quickly but I need to identify what's causing the problems. I have sa access to the server and am trying to find a way, via perfomance monitoring or enterprise manager, to work out which database, if any, is responsible for this behaviour. There are no blocks visible in Current Activity on Enterprise Manager, I'm getting average LockRequest/sec of about 2000 bit with peaks up to 31000. Processor time is averaging out at about 10%, not too scary. I can find lots of information on optimising individual databases and queries within them but no too much on identifying processes or queries that might be hitting a whole server installation in this fashion. Any advice most welcome. Jo
View Replies !
Troubleshooting Help
I need help. I have a SSIS package that connects to a ODBC database, it then does ETL from that database into a SQL 2005 database. I run the package using a Sql Agent job each night. However the package fails randomly with no apparent reason. I have setup logging and after viewing the logs I am no closer to solving the problem. Any Ideas how I can track this down? Thanks.
View Replies !
Troubleshooting ADO
This error is triggered by the following code. How do I figure out why? It works fine on an old server running win2000 but blows up on win2003 Microsoft VBScript runtime error '800a01a8' Object required: 'Param1.value' /mmd/_ScriptLibrary/getcons.asp, line 19 set conn = session("conn") ' Response.write(pg); set cmdStoredProc = Server.CreateObject("ADODB.Command") set cmdStoredProc.ActiveConnection = conn cmdStoredProc.CommandText = "sa.mmd.getConsultantList" cmdStoredProc.CommandType = adCmdStoredProc set Param1 = cmdStoredProc.CreateParameter("conam",adVarChar,adParamInput,3) cmdStoredProc.Parameters.Append Param1 Param1.value = request.querystring("q") Param1.size = Param1.value.length set rs = cmdStoredProc.Execute
View Replies !
Troubleshooting
Hello, I have tables/columns exist.How to encrypt database or mask columns if I have to send a copy of DB to microsoft to troubleshoot for a problem. Thanks.
View Replies !
Can't Make Database Role A Member Of Another Database Role In 2005.
In sql server 2000, I created some custom database roles called ProjectLeader and Developer. I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking. I'm not sure how to repeat this process in 2005? Management Studio doesn't seem to allow you to add a role as a member in another role. Is there a work around or solution for this?
View Replies !
Need Help Troubleshooting Code
Hi. I was wondering if someone could take a look at this script for me. I have it doing everything I want it to, but it won't connect to the database. I can connect to the database using GridView, etc, but I set up this form myself to see if I could get it to work. Any help is much appreciated. Here is the code behind Imports System Imports System.Data Imports System.Data.SqlClient Partial Class Prac_Beth_bethCustomers Inherits System.Web.UI.Page Protected Sub txtDate_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtDate.Load txtDate.Text = DateTime.Today.ToShortDateString() End Sub Protected Sub customerIDLabel_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles customerIDLabel.Load customerIDLabel.Text = DropDownList1.SelectedValue End Sub Protected Sub UnitPrice_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles UnitPrice.Load UnitPrice.Text = DropDownList2.SelectedValue End Sub Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click 'txtOrderTotal.Text = 'sql statements to insert items into database using Transactions table Dim conn As SqlConnection Dim trans As SqlTransaction Dim cmd As SqlCommand Try conn = New SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString) conn.Open() trans = conn.BeginTransaction cmd = New SqlCommand() cmd.Connection = conn cmd.Transaction = trans 'set transaction details cmd.CommandText = "INSERT INTO Transactions(practitionerID, customerID, ItemCode, ItemName, ItemQuantity, ItemRate, TransactionDate, OrderTotal) VALUES (@practitionerID, @customerID, @ItemCode, @ItemName, @ItemQuantity, @ItemRate, @TransactionDate, @OrderTotal)" cmd.Parameters.Add("@practitionerID, DataSqlDBType.int") cmd.Parameters.Add("@customerID, DataSqlDBType.int") cmd.Parameters.Add("@ItemCode, DataSqlDBType.VarChar,50") cmd.Parameters.Add("@ItemName, DataSqlDBType.VarChar,100") cmd.Parameters.Add("@ItemQuantity, DataSqlDBType.int") cmd.Parameters.Add("@ItemRate, DataSqlDBType.money") cmd.Parameters.Add("@TransactionDate, DataSqlDBType.datetime") cmd.Parameters.Add("@OrderTotal, DataSqlDBType.money") cmd.Parameters("@practitionerID").Value = "2" cmd.Parameters("@customerID").Value = customerIDLabel.Text cmd.Parameters("@ItemCode").Value = "2" cmd.Parameters("@ItemName").Value = "Coaching" cmd.Parameters("@ItemQuantity").Value = txtQuantity.Text cmd.Parameters("@ItemRate").Value = DropDownList2.SelectedValue cmd.Parameters("@TransactionDate").Value = txtDate.Text cmd.Parameters("@OrderTotal").Value = txtOrderTotal.Text Dim transactionID As Integer transactionID = Convert.ToInt32(cmd.ExecuteScalar()) trans.Commit() Catch SqlEx As SqlException If trans IsNot Nothing Then trans.Rollback() End If 'Tools.log("An error occurred while creating the order", SqlEx) Throw New Exception("An error occurred while creating the order", SqlEx) Return If conn IsNot Nothing Then conn.Close() End If Catch ex As Exception End Try End Sub Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click txtOrderTotal.Text = UnitPrice.Text * txtQuantity.Text End Sub End Class ---------- Here is the page <%@ Page Language="VB" MasterPageFile="~/admin.master" AutoEventWireup="false" CodeFile="bethCustomers.aspx.vb" Inherits="Prac_Beth_bethCustomers" title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="AdminPlaceHolder" Runat="Server"> <table border="0" cellpadding="6" cellspacing="0" style="width: 849px"> <tr> <td style="width: 235px" valign="top"> Select a Customer:<br /> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2" DataTextField="LastName" DataValueField="customerID"> </asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>" SelectCommand="SELECT [LastName], [customerID] FROM [Customers] WHERE ([PrimaryPractitioner] = @PrimaryPractitioner)"> <SelectParameters> <asp:Parameter DefaultValue="Beth" Name="PrimaryPractitioner" Type="String" /> </SelectParameters> </asp:SqlDataSource> <br /> <strong>Customer Details <br /> </strong> <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="customerID" DataSourceID="SqlDataSource3" Height="50px" Width="222px"> <Fields> <asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName" /> <asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName" /> <asp:BoundField DataField="Street" HeaderText="Street" SortExpression="Street" /> <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" /> <asp:BoundField DataField="State_Province" HeaderText="State" SortExpression="State_Province" /> <asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" /> <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /> </Fields> </asp:DetailsView> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>" SelectCommand="SELECT [customerID], [FirstName], [LastName], [Street], [City], [State_Province], [Zip], [Country], FROM [Customers] WHERE ([customerID] = @customerID)"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="customerID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> </td> <td valign="top"> <table border="0" cellpadding="9" cellspacing="0" style="width: 572px"> <tr> <td style="width: 102px" valign="top"> </td> <td valign="top"> <asp:Label ID="txtDate" runat="server" ForeColor="#000000"></asp:Label></td> </tr> <tr> <td style="border-top: #a9a9a9 1px solid" valign="top"> Customer ID</td> <td style="border-top: #a9a9a9 1px solid" valign="top"> <asp:Label ID="customerIDLabel" runat="server"></asp:Label> </td> </tr> <tr> <td style="border-top: #a9a9a9 1px solid" valign="top"> Item</td> <td style="border-top: #a9a9a9 1px solid" valign="top"> Coaching</td> </tr> <tr> <td style="border-top: #a9a9a9 1px solid" valign="top"> Select Rate:</td> <td style="border-top: #a9a9a9 1px solid" valign="top"> $ <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource1" DataTextField="RateMinute" DataValueField="RateMinute" AutoPostBack="True"> </asp:DropDownList> per Minute<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>" SelectCommand="SELECT [RateMinute] FROM [Practitioner_Rates]"></asp:SqlDataSource> </td> </tr> <tr> <td style="border-top: #a9a9a9 1px solid" valign="top"> Select Quantity</td> <td style="border-top: #a9a9a9 1px solid" valign="top"> <asp:TextBox ID="txtQuantity" runat="server" Width="24px"></asp:TextBox> Minutes x $<asp:Label ID="UnitPrice" runat="server"></asp:Label> <asp:Button ID="Button2" runat="server" Text="Calculate Order Total" /></td> </tr> <tr> <td style="border-top: #a9a9a9 1px solid" valign="top"> Order Total</td> <td style="border-top: #a9a9a9 1px solid" valign="top"> $<asp:TextBox ID="txtOrderTotal" runat="server" Width="66px"></asp:TextBox></td> </tr> <tr> <td style="border-top: #a9a9a9 1px solid" valign="top"> </td> <td style="border-top: #a9a9a9 1px solid" valign="top"> <asp:Button ID="Button1" runat="server" Text="Process Order" /><br /> </td> </tr> <tr> <td valign="top" colspan="2"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="transactionID" DataSourceID="SqlDataSource4"> <Columns> <asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" /> <asp:BoundField DataField="ItemQuantity" HeaderText="ItemQuantity" SortExpression="ItemQuantity" /> <asp:BoundField DataField="ItemRate" HeaderText="ItemRate" SortExpression="ItemRate" /> <asp:BoundField DataField="TransactionDate" HeaderText="TransactionDate" SortExpression="TransactionDate" /> <asp:BoundField DataField="OrderTotal" HeaderText="OrderTotal" SortExpression="OrderTotal" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>" SelectCommand="SELECT * FROM [Transactions] WHERE ([practitionerID] = @practitionerID)"> <SelectParameters> <asp:Parameter DefaultValue="2" Name="practitionerID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> </td> </tr> <tr> <td valign="top" colspan="2"> </td> </tr> <tr> <td style="width: 102px" valign="top"> </td> <td valign="top"> </td> </tr> <tr> <td style="width: 102px" valign="top"> </td> <td valign="top"> </td> </tr> <tr> <td style="width: 102px" valign="top"> </td> <td valign="top"> </td> </tr> </table> </td> </tr> <tr> <td style="width: 235px" valign="top"> </td> <td valign="top"> </td> </tr> <tr> <td style="width: 235px; height: 9px;" valign="top"> </td> <td valign="top" style="height: 9px"> </td> </tr> <tr> <td style="width: 235px" valign="top"> </td> <td valign="top"> </td> </tr> <tr> <td style="width: 235px" valign="top"> </td> <td valign="top"> </td> </tr> <tr> <td style="width: 235px" valign="top"> </td> <td valign="top"> </td> </tr> <tr> <td style="width: 235px" valign="top"> </td> <td valign="top"> </td> </tr> <tr> <td style="width: 235px" valign="top"> </td> <td valign="top"> </td> </tr> <tr> <td style="width: 235px" valign="top"> </td> <td valign="top"> </td> </tr> <tr> <td style="width: 235px" valign="top"> </td> <td valign="top"> </td> </tr> </table> </asp:Content>
View Replies !
Troubleshooting Needed
Hi, we just started receiving the following error messages about 3weeks ago. We don't get them all the time, or even on the same pagesnecessarily, but we get them from time to time and our sql serverconnection is slower in the last three weeks also. Our database isremotely hosted, and the programmers are also offsite, so we are tryingto deal with this issue with our database server host, with noknowledge of sql server. They say nothing has changed on the server,but we haven't changed any of our front end files either, so we don'tknow what the problem is. Here's 3 of the most common errors:1. Microsoft Cursor Engine error '80004005'Data provider or other service returned an E_FAIL status..../subview.asp, line 1322. Microsoft OLE DB Provider for SQL Server error '80004005'TDS buffer length too large.../subview.asp, line 283. Microsoft OLE DB Provider for SQL Server error '80004005'Unknown token received from SQL Server.../members.asp, line 182We have *no idea* what the above error messages refer to, or whetherthis is something wrong with the server or the ASP pages mentioned. Anyhelp anyone could provide so we can understand the types of messages weare getting would be nice.Regards,Sage
View Replies !
Troubleshooting Failed Job
Can anyone help me troubleshoot this failed job. The error message in eventviewer is:SQL Server Scheduled Job 'Job1' (0xB1D83B7357CF2B4C808AD1CFA34CE8F9) -Status: Failed - Invoked on: 2005-05-12 09:31:38 - Message: The job failed.The Job was invoked by User sa. The last step to run was step 1 (Job1).The job step is:EXEC proc1I can execute the procedure in QA.Proc1 does the following:INSERT values into a table in the database where the job is running fromSELECT records from a seperate database on seperate serverI'm using four part naming in the select against a linked server. I suspectthat the issue is the security between the different servers/databases but Idon't know where to get more information about the failed job or what to trynext.thanks
View Replies !
Troubleshooting SOAP
Hi,This is the first time I use SOAP. Dont really know the exact pictureyet. Hope you can shed some lights on me.May I know where should I start troubleshooting if I get the followinerror message when running SOAP on Windows 2003, SQL Server 2000 ?Error Code: 0 Error Source= Microsoft VBScript runtime error ErrorDescription: ActiveX component can't create object:'MSSOAP.SOAPClient' Error on Line 1. The step failed.Thanks.
View Replies !
Troubleshooting Deadlocks
Lock:Deadlock Chain Exchange 1 16325 2006-04-21 09:20:18.560 Parallel query worker thread was involved in a deadlock 0 Is the process I am running deadlocking with Exchange Server? The data above is from Profiler. Thanks Lystra
View Replies !
Replication Troubleshooting
Can anyone direct me to Merge replication troubleshooting guide? When I try to PULL a snapshot to a subscriber I get the error message One or tables could not be dropped because one or more tables are in use by other publications. But they aren't in use. I don't know where to look.
View Replies !
Asking For Help Troubleshooting Sql Code
Hello guys, i am new to sql so forgive me if dont see something in the code that i should. Anyway the code below comes from the sql script that belongs to "BPBlog asp blogging software" ...its not mine. Whenever i run this code in query analyzer from sql server 2000 i get an error that says : Server: Msg 170, Level 15, State 1, Line 16 Line 16: Incorrect syntax near '('. *line 16 is the below line in the code. i have been going through books online since last night to try to get an understanding of the syntax of all those keywords that sorround the error but still havent figured it out yet. any advise on the error would be sincerely appreciated. Thanx, USE [Blog] GO /****** Object: Table [dbo].[tblAuthor] Script Date: 11/07/2006 10:51:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblAuthor]( [fldAuthorID] [int] IDENTITY(1,1) NOT NULL, [fldAuthorUsername] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [fldAuthorRealName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [fldAuthorEmail] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [fldAuthorWebsite] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [fldAuthorBlurb] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [fldAuthorPassword] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Approved] [smallint] NULL DEFAULT ((0)), [fldAdmin] [smallint] NULL DEFAULT ((0)), CONSTRAINT [aaaaatblAuthor_PK] PRIMARY KEY NONCLUSTERED ( [fldAuthorID] ASC ) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
View Replies !
Replication Troubleshooting
We have some transactional replication jobs from oracle to mssql2005. I found an error in the replication monitor as below, ============================================ Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. (Source: MSSQLServer, Error number: 1018) Get help: http://help/1018 Command attempted: if @@trancount > 0 rollback tran (Transaction sequence number: 0x00000000000000000D4300000000, Command ID: 26290) ============================================ I found the corresponding table from the command id. Since some of the tables in our env. are created with triggers which cause the replication error occasionally! How can I trace the information of the transaction/record which made the replication pending? Also, there may be 'invalid' input in oracle and replicate to mssql2005 which cause error in trigger and made the replication panding. Any way to solve this kind of problem? Thanks in advance
View Replies !
Troubleshooting Scenarios
Does anyone have any good places where I can get some practice scenarios for DBA activity? Also any transact sql puzzles to solve for practice purposes. I want to get as much "real world" activity under my belt as possible in a quick time-frame. Thanks.
View Replies !
Troubleshooting Data Updates
I don't know if the title for the subject is appropriate here, anywayhere goes:This process was set up by someone and I have inherited it. I have asql2000 database that has about 13 tables that get populated with datafrom 3 different databases. I have identified where each of this datacomes from, and the stored procedures that do the updates, inserts, anddeletes, and the jobs that run these stored procedure to do theupdates, except for one table. The updates for all the other tablesare done through scheduled jobs. For the one table I know where thedata comes from and the stored procedure that needs to run to do theupdate on the table, however I have not been able to identify theprocess that runs the stored procedure.I am hoping that someone can give me a clue as to how to find out wherea stored procedure is being used - or any other hint as to how I couldgo about finding out how this table gets updated.ThanksKR
View Replies !
Replication Troubleshooting Tips
Hi all: I'm new to replication but I have already set up replication and have seen it working and failing and have gotten myself out of jams so far but there must be an easy way to administer it when things don't replicate as expected. I'm finding that I could easily kill half a day just trying to dig up information leading to troubleshooting tips. Is there documentation just on managing this feature. The regular MS Administrator's guide doesn't offer much. Currently I have a problem that if replication fails on one command I get a SQL Mail telling me of the problem but does replication continue to the next command or does it just stop until the problem is fixed? I'm finding that I am constantly checking the publisher and subscriber databases and verifying if replication is indeed doing what the msjob_commands table reports. I set the batch to commit after each transaction instead of every 100. Help...
View Replies !
Deadlock Troubleshooting !Urgent
I have deadlock problem in my application. Set up trace 1204 and restarted the server. How do I get to the report that is generated? Where is the information that is captured as a result of this trace stored? Looked at sqldiag.txt, and error logs, it isn't available Any help will be appreciated. Thanks
View Replies !
Database Maintenance Troubleshooting
SQL Server 7.0 with SP 1 Database maintenance job is failing with error about being unable to delete old backup file. I can manually delete the file with no problem. This occurs randomly, maybe once a week. Any ideas on what's wrong, and how to fix it?
View Replies !
Troubleshooting Reporting Services
Hi all My problem is, as I suppose, simple. I am working on OS XP Professional SP 2, 512 MB RAM, 80 GB HDD. I also installed IIS 5.1 I installed SQL Server 2005 Express edition with Advanced Services including Reporting Services. In some video I saw that I should configure Reporting Services on my own after instalation, so I did. Then I started Reporting Services Configuration Manager, and made my own configuration. I called Virtual Directory MyReports. When I started Internet Explorer with URL: http:\localhostMyReports I received a message like "HTTP:1.1 500 Internal Server Error" How to properly configure IIS and Reporting Services? Reporting Services is working in Reporting Services Configuration Manager Please help me out
View Replies !
Query Performance Troubleshooting Help
Hi all. I'm new to this forum and looking for some assistance. I've run into a unique (for me) performance problem. I have a select statement that performs fine ( < 1 second ) using one set of values in the criteria but very poorly ( > 3 minutes )using different values. In both circumstances the query returns zero rows. The query involves a parent-child join with the criteria spread across both tables. The execution plan looks similar between the two; the difference being a few percentage points difference on some of the operations. The tuning advisor has no recommendation in case 1 but suggests a couple of additional indexes and 4 statistics in case 2. My gut tells me that the solution is *not* applying the additional indexes/statistics but some other issue. Or it could be the sushi I just ate. Anyway, I'm hoping someone can point me in the right direction as what to analyze to determine why simply changing a single supplied criteria value would have such a dramatic effect on performance.
View Replies !
Need Help Troubleshooting Diff BackUp Job
I have a handful of jobs that I need to troubleshoot, but this one will hopefully give me enough insight to do the rest myself. I need to figure out why a Maintenance Plan that performs a Diff Backup on 4 databases, and then a transaction log back up on the 4 databases is failing. I just get the following error message, and I cannot tell what failed. Executed as user: <SERVER_NAME>SYSTEM. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:20:05 AM Progress: 2008-01-03 11:20:05.95 Source: {SOME_STUFF_WAS_HERE} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-01-03 11:20:06.35 Source: Back Up Database (Differential) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete End Progress Progress: 2008-01-03 11:20:06.35 Source: Back Up Database (Differential) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete End Progress Progress: 2008-01-03 11:20:06.37 Source: Back Up Database (Differential) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete End Progress Progress: 2008-01-03 11:20:06.37 Source: Back Up Database (Differential) ... The package execution fa... The step failed. How can I find out how to fix this problem? And please let me know if I am doing something wrong. (For instance, perhaps those two tasks shouldn't be together.) - - - - - Will - - - - - http://www.strohlsitedesign.com http://blog.strohlsitedesign.com/ http://skins.strohlsitedesign.com/
View Replies !
|
TRACKER
