Would be interested in any advice or comment on the issue we are experiencing with SQL 2005.
In order to test some DTS package migrations, we simply created a DTS package on SQL 2000 (using Export) to copy all database objects from one database to another.
Then we restored the source database on SQL 2005 server, migrated the DTS package, and tried to execute it.
The package migrated 'fine', ran part way, and then failed. Since then we have been unable to connect to the server (Database Engine, or Integration Services) using Windows Authentication. (Error 18456, Sev 16, State 11).
We can connect to the Database Engine using SQL Authentication (but not to Integration Services which only allows Windows Authentication).
We have been able to replicate this consistently - every annoying time we've tried it!
(Reinstalling SQL Server does 'fix' the issue ... until you try a similar package again).
we  do get incidents saying user can't login even adding to the group.So is there any script to check which group the user xxxxx belongs to from SSMS?
I have some script which creates/grant priveleges to windows/nt users to DB but i frequently get the message below:
Windows NT user or group '<user or group>' not found. Check the name again.
I understand that this is because the said user/group is indeed not present in the environment I'm running the script (ie. testing and production environment).
But is it possible to have some sort of checking whether the user/group exists in the environment so that I could determine whether or not to call some lines of code?
Not sure if this is possible so i had to ask. c",)
In our sql server we have around 40 windows group. Say a Windows user = "X" This X user does not have a direct windows login, he is present in one or more windows groups registered in the sql server.
I need to know throught which group he is logging in.
If I know this , this will help in my auditing process.
I am setting up security for access of database tables for members in a specific Windows User Group.I want to check in a SQL script if this Windows User group is added and if so, add database users and grant SELECT on specific tables.
I have tried this:
SELECT * FROM master.sys.syslogins WHERE name like '%FoeUsers' AND isntgroup = '1'
but that selects a SQL user or group and not a Windows Group.Is there a way to check if a Windows NT (active Directory) user group exists?
If I have a domain user DOMAINuser1 who belongs to multiple window groups say DOMAINLookupConfigUsers and DOMAINAuditConfigUsers. In sqlserver, I would create two logins - DOMAINLookupConfigUsers and DOMAINAuditConfigUsers and matching users in the database. Then I grant LookupConfig role to the LookupConfigUsers user and AuditConfig role to the AuditConfigUsers user in the database. When DOMAINuser1 logs in, will it have both roles? I try to set this up but it does not seem to work. The domain user only picks up one of the role. Am I on the right track? If not, what is the proper way to grant multiple roles to a user when it belongs to multiple groups and each group has different privileges in the database.
== I asked this question directly to Remus and wanted to share the response to all of those people using this forum == We recently moved our database server from SQL Server 2000 to SQL Server 2005. All applications on our intranet development server stay the same [VS.NET 2003], but recently resources in our Dev DB server ran out of space. While doing a thorough investigation, I noticed ERRORLOG file was occupying about 35 Gig of HDD space. I immediately checked SQL Server error log and noticed an entry which says €“ =========================================================================================== Date 7/7/2006 4:45:37 PM Log SQL Server (Current - 7/7/2006 4:45:00 PM)
Source spid77s
Message The activated proc [dbo].[SqlQueryNotificationStoredProcedure-5eaf8465-d0cb-4be7-93b6-44bb979dd41c] running on queue BW_Content.dbo.SqlQueryNotificationService-5eaf8465-d0cb-4be7-93b6-44bb979dd41c output the following: 'Could not obtain information about Windows NT group/user 'BWCINCHoffK', error code 0x534.' ===========================================================================================
What is this SqlQueryNotificationService in my database? Is it a SQL Server 2005 thing? Why the same kind of stored procedure does not exist in other databases, but BW_Content? This error is getting repeated most probably every second and is filling up our server. I believe our corporate IT people removed our domain accounts from BWCINC domain to BWCORP domain and probably some application which is using BWCINCHoffK credential is getting errored out. I tried to locate this application and was not successful. Is there anyway that I can stop this ERRORLOG from growing? How can I delete these log entries so that I can make space on our Hard Drive? Is there an easy way in SQL Server 2005 to locate which application is creating this error? Response from Remus: The 'SqlQueryNotificationService-...' is the service created by SqlDependency when you call SqlDependency.Start (). The problem you describe appears because the 'dbo' user of the database is mapped to the login that originally created this database. The SqlDependency created queue has an EXECUTE AS OWNER clause, owner is 'dbo' and therefore this is equivalent to an EXECUTE AS USER = 'dbo'. The error you see is reported by the domain controller when asked to give information about the original account 'dbo' mapps to (that is, BWCINCHoffK'): Error code: (Win32) 0x534 (1332) - No mapping between account names and security IDs was done.
To solve the issue, change 'dbo' to match a correct login, using either sp_changedbowner or ALTER AUTHORIZATION ON DATABASE::[dbname] TO [somavalidlogin] To find the databases that have this problem, run this query:
select name, suser_sname(owner_sid) from sys.databases The databses that have the problem will show NULL on the second column. A similar problem is described here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1
To remove the entries, use sp_cycle_errorlog to force a new errorlog file, then delete the huge log file. ---------------------------------------
I executed ALTER AUTHORIZATION ON DATABASE::[BW_Content] TO [sa];
I got this error in SQL Error Log once and the growth of ERRORLOG was stopped. =============================================================== Date 7/10/2006 1:16:55 PM Log SQL Server (Current - 7/10/2006 1:17:00 PM) Source spid20s
Message
The query notification dialog on conversation handle '{6BDE95F7-0EFB-DA11-9064-000C2921B41B}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-c15bb868-ed56-47d2-bf91-ce18b320989a' because it does not exist.</Description></Error>'. ===============================================================
I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.This is what worked for me before:EXEC sp_grantlogin '4800X2ASPNET'USE MyDBEXEC sp_grantdbaccess '4800X2ASPNET'EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'But for some reason now I am getting this error:Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49Windows NT user or group '4800X2ASPNET' not found. Check the name again.Msg 15401, Level 16, State 1, Line 1Windows NT user or group '4800X2ASPNET' not found. Check the name again.Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75User or role '4800X2ASPNET' does not exist in this database.I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.What weird is the old copy of this query I used had the local pc as the computer name. If I exchange the computer name from the remote pc name to the local 2003 Server machine name, the query works fine. Although that still doesn't let me add connections via Web Developer Express without error. It doesn't make sense to me anyhows, how could I have used the local machine's name and not the remote machine's name to get this to work previously. So confused.Thank you in advance for help.
I am attempting to use Visual Web Developer Express with a connection to a SQL Express db from a non-admin account on my XP Pro SP2 machine.
I can do everything in the app under an admin login, but can't seem to configure the db to allow the non-admin account access to the db. I've tried tweaking WMI, using Network Service, Local Service, and Local System with NT AUTHORITY, individual logins, and group permissions, but I'm stuck.
I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine. I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.
This is what worked for me before: EXEC sp_grantlogin '4800X2ASPNET' USE MyDB EXEC sp_grantdbaccess '4800X2ASPNET' EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'
But for some reason now I am getting this error: Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49 Windows NT user or group '4800X2ASPNET' not found. Check the name again. Msg 15401, Level 16, State 1, Line 1 Windows NT user or group '4800X2ASPNET' not found. Check the name again. Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75 User or role '4800X2ASPNET' does not exist in this database.
I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working. What weird is the old copy of this query I used had the local pc as the computer name. If I exchange the computer name from the remote pc name to the local 2003 Server machine name, the query works fine. Although that still doesn't let me add connections via Web Developer Express without error. It doesn't make sense to me anyhows, how could I have used the local machine's name and not the remote machine's name to get this to work previously. So confused.
I am creating a windows application that is not going to be used in a controlled environment like a LAN or some other network, so I want to include a local database in the install of this app. Ideally, I would like to use SQL 2005 Express like you would MSDE or Access, but I am not sure if the user must have SQL Server installed on their machine in order for this to work and I don't have a machine without it to test on.
I would like the Backup SQL user A can backup and restore DATABASE_a, what access right I need to grant to him. Although he can backup the database_a to the c:Program Files.....Database_a.BAK but he cannot restore it with some message said ''not enough security privieges..
Please quote some example right assigment so that I can replicate to our environment
I did: select city, count(*) from classifieds_address group by city You would think it would distiguish between "Koh Phangan" and "Koh Phangan ". No!!! It shows them as one row in SQL!!! Is this SQL bug? Aberdeen 1bayombong 1Brooklyn 4Castle Peak Bay 1Causeway Bay 2Chiang Mai 1Hong Kong 1Hong Kong Wan Chai 1Hong Kong Island 10Hong Kong Sheung Wan 1Karachi 1Koh Phangan 21Koh Samui 37Koh Tao 9Kowloon 13Kowloon, Tsim Sha Tsui 1Lantau Island 1Mid-Levels 3New Territories 2New York 3North Point 1Oligar 1Tsim Sha Tsui 1Wan Chai 5Yau Ma Tei 1Yau Ma Tei, Kowloon 1
Hi! I'am new to this forum and would apreciate any feedback on my problem. I have a quarry that returns the count of former customers with average cell-phone usage between 200 and 299. The ressult is grouped in year and week with group by. The dates are represented by the closingdate of the customers subscription.
The ressult is used for reporting purposses, but I need my selection to return '0' on weeks where there are "no reccords found".
CODE:
SELECT '200-299' AS ARPU, year AS YEAR, week AS WEEK, COUNT(nummer) AS Antall FROM
(SELECT SERGEL_PREPAID.SP_Mobilenumber AS nummer, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP) AS year, DATEPART(ww, TRANSLOG.TRL_TIMESTAMP) AS week, ROUND(AVG(SERGEL_PREPAID.SP_Sum), 0) AS average FROM SERGEL_PREPAID INNER JOIN TRANSLOG ON SERGEL_PREPAID.SP_Mobilenumber = TRANSLOG.TRL_MOBILE WHERE (TRANSLOG.TRL_STATUS = 'NP_FERD') GROUP BY SERGEL_PREPAID.SP_Mobilenumber, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP), DATEPART(ww, TRANSLOG.TRL_TIMESTAMP) HAVING (AVG(SERGEL_PREPAID.SP_Sum) BETWEEN 200 AND 299)) AS derivedtbl_1 GROUP BY uke, all aar
NB: Using SQL Server 2005. Any tip or solution will be a big help Best regards Gard S
i am attempting to run phpbb using ms sql 2005 on the same box but get the following error during the setup
Could not connect to the database, see error message below. [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
i am not sure yet if this is an issue with sql, php or phpbb
php is installed in iis and has all the modules installed which the php msi installer supported. i can run php code but in this case when i'm running the install.php file for the phphbb setup, i fill in the values for the database and got that error
phpbb detects all the required server settings and sees that i have ms sql installed
any suggestions on what that error means or how to go about configuring odbc driver?
probably a simple issue. but i'm still new with sql stuff
We are looking at developing an SQL Server 2005 Database and I would like to use Windows Authentication rather than SQL Server Authentication to connect our client app.
In our development environment, we have two Servers, one being used as a file server and the other as an SQL Server. We have now set up a domain using the file server as the domain controller. (We had previously been set up to use a workgroup).
I have set up an active directory group called SqlDevelopers and added an active directory user called Jonathan to it.
On the SQL Sever, in management studio, I have set up a new server login which uses windows authentication called DomainSqlDevelopers. I used the GUI to verify I could see the domain and the group.
The default database is set to a test database on the server. A user in the test database is mapped to the DomainSqlDevelopers and given the Roles dbo, db_datareader, db_datawriter.
To test the log in, on the server, I logged out as administrator and in as Jonathan. I could successfully access the server through management studio using windows authentication.
However, if I log in as Jonathan on my client PC and try to access the SQL Server using management studio and windows authentication, I have problems.
The first time I try I will get a timeout error. If I try again will get either:
Login failed for user ''. The user is not associated with a trusted SQL Server connection
Or
Cannot generate SSPI context
I can€™t determine any pattern to which of the above errors I get.
However, if I log in as administrator on my client PC, I can connect to the server using management studio and windows authentication.
Sounds like Active Directory/Domain or other Network issue (Not really my area). I would be grateful for any help.
I have a user who cannot login to a .mdb when he is logged in to Windows, on his workstation or any other. No one can login to the .mdb when he is logged in to Windows.
He CAN login to the same .mdb if someone else is logged in to Windows, either on his workstation or any other.
When the user logs in he gets the following error:
MS SQL Server Login
Connection Failed:
SQL State: €˜28000€™
SQL Server Error 18456
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user €˜domainusername€™
I have removed and recreated the ODBC Connection while he is logged in. I have tried changing the trusted login to administrator (in the window that pops up when he fails to connect).
He has the permissions he needs to the .mdb because he can login under anyone else€™s profile.
I tried copying his profile to a brand new user and ended up with the same result.
This leads me to wonder if there is some corruption in one of his profile€™s .dat files or ???
This is the boss€™ account and he does not want to be given a new username and profile€¦
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Running on MS Windows 2003 Server Standard SP1
All workstations are XP SP2 with MS Access 2003 SP2 (from Office Pro distro)
We use roaming profiles. All systems within the same physical and logical network and domain.
Any help you can provide will be greatly appreciated.
Please let me know if there is any additional information you need.
Hi, I'm having a problem installing SQL2005 Developer on Windows Vista.
At the System Configuration check page, I'm getting the IIS Feature Requirement item telling me IIS is not installed or disabled. This is not the case.
I have read article http://support.microsoft.com/kb/920201 and switched on the IIS components as specified.
I've tried installing with Firewall, virus checker etc switched off. Every time I get the same problem. This is an issue, as I specifically need Reporting Services installed.
Greetings,VWD EE and other Tools do not have problems working O.K. on my machine, when I am logged-on as restricted(limited) user, both environment and local web server are functioning, and it was MS effort to do it right.BUT the Problem is, when I try to connect to database->new DB connection(either through vwd or management studio etc). I get an Error - which should be written to event log (according to msg) but it isn't - that I can't connect. I am sure that it is because NO SQL SERVER2005EXPRESS instance is running, which I think is the only prerequisite to have it work (I do not need sqlbrowser service I am doint local development).SQL2005express service is configured on MANUAL start. I can start it as administrator through SQL config manager, but it is not convienient and what I want. I need to start it ONLY as a developer user, I do not want it to be running all-the-time for everyuser using computer. It was congigured as Network Service logon, I tried Local Service logon, and I even tried configuring it to logon as -my developer user- account, e.g. with limited user name and his password.In every case I can't start the service as member of users group and this developer. Then I added limited user to SQL2005EXPRESS group. Still NO help, won't start either.My question is, If I want to start developing ADO2.NET application and I need to have running SQL2005Express instance as a develper, how can I start it? I think I do not have some rights to masterDB or something. Do it allways need to be running when computer starts? Isn't there any other way, to start it JUST when I think I start developing?I know of user-mode of accessing SQL2005express DBs, but it also assumes that SQL2005Express service is already running, what I am trying to prevent. I do not want to have it running for everyone who uses computer, just for someone who neeeds it.Any help explaining me the right way HOW TO SETUP WORKING ENVIRONMENT when developing as limited user welcome, I read the the documentations and haven't found the answers.THANK YOU !
i have recently started to work with the analysis services and i am having some problems.
i have made a web application that for now just tries to connect to an analysis server.
The server is installed on an xp sp 2 system.
for testing, i used my local computer, which is running also win xp sp2. After deploying, the application can connect to the AS server on the other machine. the problem occurs when i try to deploy the applicatuion on the production server, which is running win 2k3 server on 32 bit. When deploying, i use the same connection string, so it should try to connect to the AS server installed on a win xp sp2 system.
The first thing that came to my mind was to check the MDAC versions, be cause of the message: "Provider cannot be found. It may not be properly installed."
on my computer, where the application worked ok, the version is MDAC 2.8 SP 1 Windows XP SP 2
on the win2k3 server, the MDAC version is : MDAC 2.8 SP2.... i thought it was the version of the service pack, so o searched for the update of MDAC 2.8 SP1, trying to install mdac 2.8 sp2 to see if i was to see the same error, but i found that mdac 2.8 sp2 is only for win2k3 server, and for win xp is mdac 2.8 sp1, according to this link: http://support.microsoft.com/default.aspx/kb/884103
Now i got confused and have no clues how to make this work...
Will it be possible to do an in-place upgrade from SQL 2000 Server Enterprise SP4 32 bit running on top of 64 bit Windows 2003 Enterprise , clustered, to SQL 2005 Enterprise 64 bit? The 32 bit SQL 2000 to 64 bit SQL 2005 in place upgrade seems questionable to me... Anybody tried anything like this?
Moss 2007 is installed on one server while sql2005-Sp2 is installed on a seperate server. I installed the web front-end of sharepoint services 3.0 on my report server. I also changed the port of my default website from 80 to 8080 so that they dont conflict.
I run the reporting service configuration app and created a new report database that had sharepoint integration switched on. Furthermore I created a new app pool in IIS that has a domain account instead of network service (as described in the microsoft instructions) and used another domain account as the windows service identity .
After that I installed the sharepoint add-in on the moss 2007 server. After doing so the reporting configuration app showed a green light for sharepoint integration. I went to application management and found the section for reporting services.
Now... allthough Manage integration settings and Grant database access work the third option "Set server defaults" produces the following error:
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized.
When I try to access the report server through http: i.e. http://bla:8080/reportserver I get :
Report Server has encountered a SharePoint error. (rsSharePointError) Get Online Help User cannot be found.
My trace log file complains about the following: (around 10 times the same message per try)
w3wp!security!1!4/17/2007-19:00:31:: i INFO: Exception while running with elevated privileges w3wp!security!1!4/17/2007-19:00:31:: i INFO: The permissions granted to user 'NTSRVDOMAINAdministrator' are insufficient for performing this operation. w3wp!library!1!4/17/2007-19:00:31:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'NTSRVDOMAINAdministrator' are insufficient for performing this operation., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'NTSRVDOMAINAdministrator' are insufficient for performing this operation. w3wp!security!1!4/17/2007-19:00:31:: i INFO: Exception while running with elevated privileges w3wp!security!1!4/17/2007-19:00:31:: i INFO: The permissions granted to user 'NTSRVDOMAINAdministrator' are insufficient for performing this operation. w3wp!library!1!4/17/2007-19:00:31:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. ---> Microsoft.SharePoint.SPException: User cannot be found.
Please help.. I've now spent 2 days on this and I am getting nowhere!!
I found sql2005 Enterprise cannot be installed under Simplied Chinese win 2003, the only thing i suspect is that win2003 is an envaluation copy with netframework 2.0 be installed.
Am using SSMS to add users to a DB and here is an issue I cant conquer.
User id's in this format "Aname" work as designed User Id's in this format "John P User" will not be recognized by ssms as valid windows user names even though "John P User" is Administrator of the local computer or even Domain Administrator. I have tried a script to enter the name and still the multiname format errors out where the normal naming format works every time..
Im new to the forum and very green in this arena, I have scanned 43 pages looking for a similar issue and have found nothing even remotely similar in a post.
I would be thankful for any bones you wise people could throw my way.
Details: OS Vista Ultimate (64-bit), Harware: Intel Core Duo 2 2.13 Ghz, 4 GB RAM, Serial ATA WD Raptor 150GB.
Installed all the service packs for Vista. All hardware configured and running. IIS is running, .Net 2.0 is running. During the install I get green flags across the board for all items. Regardless of what I install, when it gets to installing the DB Engine it throws an error:
TITLE: Microsoft SQL Server 2005 Setup ------------------------------
SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Windows NT user or group 'MARK-PCSQLServer2005MSSQLUser$MARK-PC$MSSQLSERVER' not found. Check the name again.. Refer to the server error logs and Setup logs for detailed error information.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40CnfgSQL%4080%40x3c29
------------------------------ BUTTONS:
&Retry Cancel ------------------------------
The url above takes me to a web page with this:
ID: 29521
Source: setup.rll I have tried not using the built in system account locally on my machine which I am logged into as with Admin rights. I have tried creating another Admin level account, and using it. I have tried using the accounts listed above specified as "domain account", and have always specified Multi Mode Authentication and set a password for the "sa" account.
I have groups of records in a table, and I would like to set a necessary condition on each group. The condition is that EXACTLY ONE of the records in each group has a flag field set to True (bit = 1). I can naturally write triggers for update, insert and delete events that test for such a condition.
Something along the lines of this condition:
(select count(ClovekAutoID) from TableOfClovekNames tCN where JeHlavni = 1 group by ClovekAutoID having COUNT(JeHlavni ) > 1) = 1In fact,
I tried this just on whim, but naturally, the SS engine told me to go roll my hoop, that subqueries are not allowed in constraint expressions.
