I've got an app that's poorly designed and doesn't do connection pooling. It causes the login rate/sec on our prod server to jump up to 15/sec. I'm trying to qualify the resource cost of this behaviour. Althought this application logs in and out, it only ever has one active spid. It never holds anything in the db, it's purely an auth cost. I'm currently trying to correlate this with SQL Server cpu time, and memory.
Does anyone know where I can find some documentation, or some tips as far as login rate is concerned?
We're planning to install a Server 2005 Failover Cluster and I'd like to find more information about licensing, etc. Basic questions:
- What is the best O.S. to run? Win 2003 or the new 2008? And what version (datacenter, enterprise)? - Since I'm going to use 2 machines, will I have to pay for 2 licenses (sql and windows - two lic. each)? Or just 1 license, since just 1 machine will be the active server?
This is for some web applications, so, web environment.
I've been struggling with this for some time. we have to group data based on Patients admission date and discharge date. If any Patients discharge date + 1 = admission date then we have group both rows into one row and sum costs from both the rows. Please check out the sample input and expected output for details.
please let me know if I need to post this on another SQL forum, and I'll be glad to do so.
I'm trying to set up the equivalent of Integrated Security=SSPI for SQL Server 2000 on IIS5.0. It's my understanding the SSPI string above is for Win NT, but not for Active Dir 2000 domains.
I'm using ASP 2.0 (NOT .Net), so please apprise me to what info I'd need in the connection string.
Currently it reads like this:
Set cn = CreateObject("ADODB.Connection") cn.CursorLocation = 3 'Client side cursor cn.Open "Provider=SQLOLEDB;Data Source=191.181.92.73;Integrated Security=SSPI"
Logins failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (microsoft SQL Server, Error: 18452)
Hi Guys.
How are you, its something very complicated to understand my issue. i am receiving the above error message when i tried to login with using windows accout to connect to SQL server Instance using client tool.
ok Let me start step by step. 1) Initially we have one box using windows 2000 (OS) and having SQL Server 2000. 2) Here on SQL Server instance i have created windows account login and gave permissions to one of the database on that instance. 3) this windows account is in the right domain and also in the same domain 4) when i right click on Management Studio on the different machine --> Run As--> enter the windows account --> select the above Sql server instance.then connect 5) Here i am not able to connect to the sql server. the below error generates when i press the connect button Logins failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (microsoft SQL Server, Error: 18452)
if we gave the full permission to connect to box with this windows account then i am able to connect the sql server instance, otherwise its not allowing me to connect to the Sql server instance.
we have another box using windows 2000 (OS) and having SQL Server 2005. i have followed the same as above with same windows account login. Here i am able to connect to the SQL Server Instance and also this windows account donot have permission to connet the box.
Anybody have idea about this issue??? if the SQL server 2000 doesnot have the option to connect remotely. unless that windows account have the full permissions to box. if you have any white paper please let know. i really appreciate for your effort, if you dont understand please let me know. thanks so much.
We have SQL server 2000 on windows 2000. From our desktop MS Access was connected to database through ODBC. Since yesterday (after our consultant had updated another asp application in the same server) we are getting €œSQL Server Error: 18452. €¦.. Login failed for user €˜(null)€™. Reason: not associated with a trusted SQL Server connection. €œ
The consultant saying he did not touch any settings on server (?). And the problem is even server€™s security property shows it has mixed mode authentication. But I cannot connect to server even through my SQL server enterprise manager in windows authentication mode. I can connect if I use SQL server authentication. Through ODBC I can connect also and test fine, if I use SQL authentication. But the Access application wouldn€™t connect using SQL authentication in ODBC.
So, can any one help me to find out what else could be done to make sure that the server is going to accept windows authentication. I€™m thinking any other settings or registry change €¦ etc. Please help.
I am trying to do a unattended upgrade of an MSDE 2000 named instance, say MyInstance, to a SQl Server Express Advanced edition. The set up is Mixed Mode Authentication. ( I can't use Windows Authentication for business reasons). The error I get is
SQL Server Setup Cannot Upgrade the specified instance by using SQL Server Creditial. You must use Windows Authentication creditial for the upgrade. Details are as follows: OS: Windows XP Prof (SP2). Original install: MSDE 2000 Instance name: MyInstance Authentication: SQL Registry Entry: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMyInstanceMSSQLServer Key LoginMode has a value of 2 ( corresponding to Mixed Mode Auth)
Upgrade attempted to: SQL Server Express Adv. Command Line: start /wait setup.exe /qn LOGNAME=C:setup.cab /settings E:Sql2005SqlTemplate.ini
The error I get in Summary.txt: Machine : xxxxxXP Product : SQL Server Database Services Error : SQL Server Setup cannot upgrade the specified instance by using SQL Server credentials. You must use Windows Authentication credentials for the upgrade.
Can't I use SQL Authentication? I could not find a definitive answer on this one. Here's one thread that seem to indicate the same issue
My questions: - Can I use SQL Authentication? - Where am I going wrong? - I know the error message says to use Windows Authentication but I don't understand why I am I forced into using that.
Any insightsopinions will be highly appreciated. I am on the verge of a breakdown :(.
I am trying to do a unattended upgrade of an MSDE 2000 named instance, say MyInstance, to a SQl Server Express Advanced edition. The set up is Mixed Mode Authentication. ( I can't use Windows Authentication for business reasons). The error I get is
SQL Server Setup Cannot Upgrade the specified instance by using SQL Server Creditial. You must use Windows Authentication creditial for the upgrade.
Details are as follows: OS: Windows XP Prof (SP2). Original install: MSDE 2000 Instance name: MyInstance Authentication: SQL Registry Entry: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMyInstanceMSSQLServer Key LoginMode has a value of 2 ( corresponding to Mixed Mode Auth)
Upgrade attempted to: SQL Server Express Adv. Command Line: start /wait setup.exe /qn LOGNAME=C:setup.cab /settings E:Sql2005SqlTemplate.ini
The error I get in Summary.txt: Machine : xxxxxXP Product : SQL Server Database Services Error : SQL Server Setup cannot upgrade the specified instance by using SQL Server credentials. You must use Windows Authentication credentials for the upgrade.
Can't I use SQL Authentication? I could not find a definitive answer on this one. Here's one thread that seem to indicate the same issue
My questions: - Can I use SQL Authentication? - Where am I going wrong? - I know the error message says to use Windows Authentication but I don't understand why I am I forced into using that.
Hi All,I'm a relative newbie to SQL Server, so please forgive me if this is adaft question...When I set "Show Execution Plan" on in Query Analyzer, and execute a(fairly complex) sproc, I note that a particular query is reported ashaving a query cost of "71% relative to the batch" - however, this isnowhere near the slowest executing query in the batch - other querieswhich take over twice as long are reported as having costs in theorder of a few percent each.Am I misreading the execution plan? Note that I'm looking at thegraphical plan, and am not reading the 'estimated' plan - I'm usingthe one generated from executing the sproc. My expectation was thatthis would be based on the execution times of the queries within thesproc, however, this does not appear to be the case. (Note - Idetermined execution times from PRINT statements, using GETDATE() todetermine the current time, down to milliseconds).Any feedback would be of great assistance... I may well have tochange the way I approach optimizing queries based on these findings.Thanks,LemonSmasher.
I have a temporary table (@tblResults) that has 4 columns that need to be populated with a calculation made from columns held within 2 other tables.
Joins @tblResults tr JOIN dbo.MarketPrice mp ON tr.Item = mp.Item AND tr.[Month] = mp.[Month] AND tr.[Year] = mp.[Year] AND mp.[Date] BETWEEN tr.LatestStartDate AND tr.PriorEndDate
[code]....
Where the 2 dbo.MarketPrice and dbo.MillDifferentials date fields are NOT equal, the last (chronologically) dbo.MillDifferentials.Diff value should be used (or '0' if no previous value found).
so expected results where @tblResults.Id = 1:
The dbo.MarketPrice.Price value of '2014-10-29' should be combined with the dbo.MillDifferentials.Diff value of '2014-10-06' - this produces the combined Max value of 184.50 The dbo.MarketPrice.Price value of '2014-04-28' should be combined with the dbo.MillDifferentials.Diff value of '2014-04-28' The dbo.MarketPrice.Price value of '2014-01-22' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' The dbo.MarketPrice.Price value of '2014-01-21' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' - this produces the combined Min value of 111.50 The dbo.MarketPrice.Price value of '2014-01-04' should be combined with '0.00' if there is no matching or previous dbo.MillDifferentials.Diff value OR the top 1/max (most recent) dbo.MillDifferentials.Diff value if a record is found before the specified @tblResults.LatestStartDate
For using different services of SQL SERVER 2005 which is better... Windows Authentication or SQL Server Authentication? what are the advantages and disadvantages of both?
Hello guys... I have been struggling for quite some time trying to have the authentication page on my website work :-( and I'm now desperate for some help. I have used VWD to create a website and I have a login.aspx page that authentiocate users based on their credential... all of it created with the web admin tool. Now guess what! I have an instance of SQL 2005 running on my PC and it all works smoothly (users are screened and redirected to pages according to their status) but when I'm uploading the website to the company's server which is only authorized to run on SQL 200 I get the following error message:
Server Error in '/newmedical/webtools' Application.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
Does ASP.net website management (for roles and membership) create a db: ASPNETDB.mdf ( for user credentials) only accessible by SQL server 2005? If that's the case then no wonder the connection is timing out since the company's server runs on Sql 2000 Entreprise. I can't install SQL server 2005 on the server. Any idea to correct that problem? Thanks in advance for your tips and advicesJY
I can consume the reportingservice using credentials to get reportnames and folders and such, but when i go to use the reportviewer, after i set the reportpath and serverurl, I am always asked to authenticate when using the sample reportviewer with RS 2000. How can i prevent this? I know there is the Icredentials that can be set using the serverreport property with the new viewer in 2005, but how about doing the same type thing in 2000?
I'm working on deploying an ASP.NET 2.0 app onto a Windows 2003 server and I'm having trouble with SQL 2000 authentication. When I run my app from my development box running IIS 5.1 I have no problems. In order to get it to work on my this machine, I had set the account for anonymous access to run under an ASPNETDOMAIN account and configured my SQL database to allow this account.On the Windows 2003 box with IIS 6.0, I've done the same thing with anonymous access for the web app, and also created an application pool that the app runs under and have set the application pool to run under the same account. It appears the app is looking for a local SQL 2005 instance (which doesn't exist). Why would it do this and what do I need to change for this to work?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)Stack Trace:[SqlException (0x80131904): 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)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735043 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +68
Just spoke w/ our network admin and he mentioned that we use SPI, so being brand new to ASP, i did some googling and found this string:
the username and password is the account i have for the database, which is windows authentication, so i made sure the domain was in there. i am unsure of what i am doing wrong.
I just removed (via Add and remove programs) SQL 2000 Server and now only have SQL 2005 left on my machine. The 2005 Server (SQLExpress?) is set up for mixed mode and I've verified that the registry setting is 2 for LoginMode, yet I'm still unable to login. This is the following error?
TITLE: Connect to Database Engine ------------------------------
Login failed for user 'drafttrackuser'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------
Naturally, the link goes no where. I've searhed this site and it seems most of the problems are either more complex (linked servers, clusters etc.) or are people who haven't set the server to work in mixed mode. I've rebooted a couple of times but the errors persist.
I recently needed to upgrade a sql 2000 machine to 2005. Instead ofupgrading the sql 2000 default instance, I installed sql 2005 as aseparate named instance so we could go back to sql 2000 if necessary.The web server that connected to the new server didn't have anythingchange on it except the database ip was changed tomachinenameinstancename instead of just machinename. SQLauthentication was being used and the exact same userid/password wascreated on sql 2005. I logged into the database server using the sameuserid/pass to confirm it worked.However, when using the app, it reported "Login failure for: username"error and I confirmed 600 times over the next two hours that it was infact correctly typed in. The "State" in the sql server log indicated apassword mismatch, but again, it was correct and I verified it workedusing the sql 2005 management studio from the web server itself.Finally I had the bright idea of making the password blank, figuring itwas getting mangled somewhere in the process...That worked. So my question is, what is going on and how can I set anormal password? There is no encryption of the connection string andnothing changed in the web app between converting the machine from 2000(worked fine) to 2005 (password mismatch).
I wonder if it is possible to set forms authentication for report manager but leave report server "as it is". I need to authenticate users from external LDAP and can't use windows authentication for report manager, but I would also like to leave report server open for anonymous users. In that way authenticated administrators could create reports which anonymous users could read.
I tested the Security Extension Sample and got it working when I rewrote the authentication part with my own LDAP authentication.
If I have understood correctly, the report manager is just application inside report server so is it possible to use forms authentication with one application but still leave the report server with Windows authentication?
Hello, I have run into a strange problem with RS 2000. I have a base class that will call a reporting server web service, render a report to pdf, then write the report output to the stream. This has worked fine for me for reports.
However, when I render a graph to pdf, I get the following exception:
I only get this exception in our staging environment, not locally.
Here is a code snippet from the ReportServerProxy class, but it is pretty textbook according to the MSDN samples..
public class ReportServerProxy : ReportingService { protected override WebRequest GetWebRequest(Uri uri) { HttpWebRequest request; request = (HttpWebRequest)HttpWebRequest.Create(uri); // Create a cookie jar to hold the request cookie CookieContainer cookieJar = new CookieContainer(); request.CookieContainer = cookieJar; Cookie authCookie = AuthCookie; // if the client already has an auth cookie // place it in the request's cookie container if (authCookie != null){ request.CookieContainer.Add(authCookie); } ...
}
As I said, I can run reports one after another without issue, graphs are causing me problems.
I need to figure out what kind of Authentication , I need to use for following applicaiton
Product : -
1 ) It resides on a its Domain and has access to Database on that Domain. 2 ) We have a application level login , n based on application login id display specific pages.
The question that bother me is this
Q ) If i use NT authentication , then a user will be required to
a ) Login to domain (with userid and password) first and then
b ) Then i would require to again login to applicaiton with application level login and password.(different levels of login as there) Based on the application level login i will display only specific asp pages. They have different access rights..and roles.
Requirment is to login only once..and it should authenticate to application display specific pages and authenticate to SQl server database also..
Is there any way thru which i can map my application level login to SQl server.. and what authentication should i use..
I folks.I Have installed sql server 2005 express and choosed windowsauthentication on instalation, but i make a mistake and now i needmixed authentication, how can i modify this whithout uninstall andinstall again the application?thanks for the help.
Hi there,I have installed MS SQL Server 2005 on my machine with windows authentication. But now I want to switch the authentication mode to SQL Authentication. I am unable to switch, I can’t find the proper way to do so here in 2005.Could any one help me in doing this?Thank you,-Ahsan
(Using win2k, sqlserver2k, framework 1.1) I have an fairly data-heavy application that uses Windows authentication (Trusted connection/aspnet account) to connect to Sql Server. The site uses IIS basic authentication.
On the dev server everything works fine but when I move to the live server things get strange and it starts to crawl along. (Pages load OK but then it just crawls as it loads the datagrids etc. Sometimes it brings back incomplete/incorrect data )
BUT When I use Sql Authentication to connect to Sql Server and there is no problem at all!
Ok, there is something obviously wrong with the live server (which is identical setup to dev)but I dont know where to start.
I've got two applications which both have a database on my MS SQL 2000 server. The problem is, one application must use Windows Integrated Authentication (which it is currently using and cannot be changed) whilst the other application which I'm trying to configure must use a SQL password.
Since the server has already been configured to use Windows Integrated Authentication for the existing database and application, how do I configure the other database to use the SQL password?
My work is using a shared application which accesses a MSSQL 2000 database. To access the application, the folder on the Windows 2003 Server is shared and users can access the folder through a shared drive.
For the application to access the database, it uses an ODBC connection to the MSSQL server which originally used the SA password.
We have recently switched to using Windows Integrated Authentication because we believe it offers a higher level of security. However the only way in which we have been able to enable this is to add the windows users to the SQL server.
The problem with this is that the application sets permissions for individual users on what records they can see within the database. We have found that by adding the windows users to the SQL Server, they can bypass the permissions the set by the application by simply using any application that can use an ODBC connection, such as Enterprise Manager, and see all the database.
One way around this would be to set up domains of users with access privileges to the tables which reflect the permissions set by the application, and configuring a view of the data so they may only see the records that they have permissions to. However to do this would require a high administrative cost to ensure that changes made in the application are reflected in the privileges of the SQL server.
Instead, is there a way the SQL server can authenticate that the ODBC connection is coming from the correct application using Windows Integrated Authentication?
This would allow the applcation to determine security, and stop users from connecting to the SQL server using other applications.
Alternatively, can the SQL server, using Windows Integrated Authentication, also ask the application to supply a username and password?
Any help with this matter would be greatly appreciated.
Hi,I'm using SQL Server 2005. My Connection String looks like that at the moment: <add name="LocalSqlServer" connectionString="Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx" providerName="System.Data.SqlClient"/> Now I'd like to change this kind of authentication to Integrated Windows AuthenticationI added the WorkerProcess IIS_WPG to the permitted Users but it didn't help.Changed the Connection String to this:connectionString="Server=xx;Database=xx;Trusted_Connection=True;"All I'm getting is that my NetworkService is not permitted to access DB when I try to connect to the DB in ASP.NET.How can I properly configure that? Thanks!
Say, I have configured my SQL to use Mixed Authentication. Now, I have a applicaiton which uses my SQL Server. The application just creates a database in SQL Server and uses the database to store its information.
This application also has a SYSTEM DSN under ODBC through which it accesses the database. For the application to access this database, should I only use SA (as my SQL instance is configured to use Mixed Authentication) or can I use Windows Authentcation too...
If I should only use SA, do we have a documentation which talks about this.
I was trying to transfer a SQL Server 2000 database to SQL Server 2005 using SQL Server Objects Task. However, The following error message was encountered: "[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".€œ
I have created an EndPoint in SQL Server 2005 as per the code below.
CREATE ENDPOINT OSTC_LMS_Endpoint
AUTHORIZATION LMSEndPointUsers
STATE = STARTED
AS HTTP(
PATH = '/ostc_sql_endpoint',
AUTHENTICATION = (BASIC),
PORTS = (SSL),
SITE = 'OSTC-DEV-001'
)
FOR SOAP (
WEBMETHOD 'ostc_SQLSoapTester'
(name='OSTC_LMS_06.dbo.ostc_SQLSoapTester',
FORMAT = ROWSETS_ONLY,
SCHEMA=STANDARD),
WSDL = DEFAULT,
LOGIN_TYPE = MIXED,
SCHEMA = STANDARD,
DATABASE = 'OSTC_LMS_06',
NAMESPACE = 'http://tempUri.org/'
)
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [LMSEndPointUsers]
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [ostc-dev-001endPointUsers]
GO
----------------------------------------------------------------------------------------------------------------- The SPROC being exposed as the webmethod: -
The computer in question is our dev server and is running as a workgroup machine with the following: - Win Server 2003 SQL Server 2005 .net Framework 2.0 No firewalls or Proxies are in the way.
The computer has to be as a workgroup machine to reflect our live server.
The user LMSEndPointUsers is a SQL Server Login The user ostc-dev-001endPointUsers is a machine login
We have employed the Security class as per the information given in the SQL Server documentation with the intention of using the WS-Security headers that apparently are to be used when trying to authenticate using a SQL Server login.
I have 7.0 server running with sp2 on NT box sp 5. This Sql server is configured to take both sql and NT authentication. Somehow, the bridge between NT and SQL server doesn't seem to be working as sql server does not recognize any NT authentication. Is there any way to fix it?
Because of this problem, I am unable to schedule any DTS package as sql server security doesn't recognize the sqlagent startup account and gives a login failure error.
I am about to open a ticket with Microsoft but before that if anybody can suggest me anything..
